{"id":2694,"date":"2014-11-09T10:59:35","date_gmt":"2014-11-09T18:59:35","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/kimberly\/?p=2694"},"modified":"2020-10-30T12:44:10","modified_gmt":"2020-10-30T19:44:10","slug":"high-performance-procedures","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/high-performance-procedures\/","title":{"rendered":"Building High Performance Stored Procedures"},"content":{"rendered":"

[NOTE: Update 12 Nov 2014. The most recent bug (KB article\u00a0KB2965069<\/a>) is THANKFULLY very unlikely for most environments (mentioned below). As a result, I’m glad to say that we can use OPTION (RECOMPILE) as a much\u00a0easier (and safer) solution. So, while I’ll still leave solution 3 as an option if you run into troubles with OPTION (RECOMPILE), I can luckily say that if you’re on the latest SP\/CU – the problems are incredibly unlikely. The BEST hybrid solution is shown below as solution 2.]<\/p>\n

In my SQL PASS Summit 2014 session last week, I spoke about building high performance stored procedures and properly dealing with parameter sniffing problems… if you’re interested, the session was recorded for PASStv and you can watch it here<\/a>.<\/p>\n

UPDATE October 2020:<\/strong> Recently, I gave this session for a PASS DBA Virtual Chapter (September 2020) and you can view this through the PASS site (PASS.org \/ Learn (drop-down menu) \/ All Recordings \/ Filter for Tripp). The first in the list will be the most recent delivery. I did a long demo explaining Code Coverage testing vs. Plan Stability testing. This is a great session to have your developers watch! Then, for more detail: consider the Pluralsight courses on Optimizing Procedural Code (Part 1 and Part 2). Thanks!<\/strong><\/p>\n

It all ties to something incredibly common in development – I call it the multipurpose\u00a0procedure. Simply put, it’s one application dialog with many possible options and then sitting behind it – ONE stored procedure. This one stored procedure is supposed to handle every possible case of execution with multiple parameters. For example, imagine a dialog to search\u00a0for a customer.<\/p>\n

\"CustomerSearch\"<\/a><\/p>\n

The user can enter in any combination of these elements and the procedure’s header looks like this:<\/p>\n

\nCREATE PROC [dbo].[GetCustomerInformation]\n(\n    @CustomerID      INT = NULL\n    , @LastName\t     VARCHAR (30) = NULL\n    , @FirstName     VARCHAR (30) = NULL\n    , @MiddleInitial CHAR(1) = NULL\n    , @EmailAddress  VARCHAR(128) = NULL\n    , @Region_no     TINYINT = NULL\n    , @Cust_code     TINYINT = NULL\n)\n...<\/pre>\n
And then the procedure’s main WHERE clause looks like this:<\/p>\n
\nWHERE ([C].[CustomerID] = @CustomerID OR @CustomerID IS NULL)\n    AND ([C].[lastname] LIKE @LastName OR @LastName IS NULL)\n    AND ([C].[firstname] LIKE @FirstName OR @FirstName IS NULL)\n    AND ([C].[middleinitial] = @MiddleInitial OR @MiddleInitial IS NULL)\n    AND ([C].[EmailAddress] LIKE @EmailAddress OR @EmailAddress IS NULL)\n    AND ([C].[region_no] = @Region_no OR @Region_no IS NULL)\n    AND ([C].[cust_code] = @Cust_code OR @Cust_code IS NULL)GO<\/pre>\n
Or, possibly like this:<\/p>\n
\nWHERE [C].[CustomerID] = COALESCE(@CustomerID, [C].[CustomerID])\n    AND [C].[lastname] LIKE COALESCE(@lastname, [C].[lastname])\n    AND [C].[firstname] LIKE COALESCE(@firstname, [C].[firstname])\n    AND [C].[middleinitial] = COALESCE(@MiddleInitial, [C].[middleinitial])\n    AND [C].[Email] LIKE COALESCE(@EmailAddress, [C].[Email])\n    AND [C].[region_no] = COALESCE(@Region_no, [C].[region_no])\n    AND [C].[cust_code] = COALESCE(@Cust_code, [C].[Cust_code]);<\/pre>\n
Or, maybe even like this:<\/p>\n
\nWHERE [C].[CustomerID] = CASE WHEN @CustomerID IS NULL THEN [C].[CustomerID] ELSE @CustomerID END\n    AND [C].[lastname] LIKE CASE WHEN @lastname IS NULL THEN [C].[lastname] ELSE @lastname END\n    AND [C].[firstname] LIKE CASE WHEN @firstname IS NULL THEN [C].[firstname] ELSE @firstname END\n    AND [C].[middleinitial] = CASE WHEN @MiddleInitial IS NULL THEN [C].[middleinitial] ELSE @MiddleInitial END\n    AND [C].[Email] LIKE CASE WHEN @EmailAddress IS NULL THEN [C].[Email] ELSE @EmailAddress END\n    AND [C].[region_no] = CASE WHEN @Region_no IS NULL THEN [C].[region_no] ELSE @Region_no END\n    AND [C].[cust_code] = CASE WHEN @Cust_code IS NULL THEN [C].[cust_code] ELSE @Cust_code END<\/pre>\n
But, no matter which of these it looks like – they’re all going to perform horribly<\/strong><\/span>. An OSFA procedure does not optimize well and the end result is that you’ll get one plan in cache. If that was\u00a0generated from\u00a0a different combination of parameters\u00a0than the ones you’re executing, you might get an absolutely abysmal plan. The concept is fairly simple – when a procedure executes and there isn’t already a plan in cache (for that procedure), then SQL Server has to generate one. To do so it “sniffs” the input parameters and optimizes based on the parameters sniffed. This is the plan that gets stored with the procedure and saved for subsequent executions. Depending on your workload characteristics, you might end up with a very common plan in cache and other users also executing the common parameters might see reasonable performance (especially if the WHERE\u00a0does NOT use LIKE and has nothing but equality-based criteria [those aren’t quite as bad as this one]). But, there will still be cases where an atypical execution performs horribly.<\/p>\n
And,\u00a0unfortunately, sometimes the worst combination happens – a very atypical execution is the one that gets sniffed and then everyone suffers.<\/p>\n
So, the admin might update statistics or force a recompile (or, even restart SQL Server) to try and get around this problem. These things “fix” the immediate problem by kicking the plan out of cache but they are NOT A SOLUTION.<\/p>\n
Solution 1: The Simple Solution – OPTION (RECOMPILE)<\/h2>\n
Since SQL Server 2005, we’ve had an option to add OPTION (RECOMPILE) to the offending statement. When OPTION (RECOMPILE) works – it works incredibly well. However, there have been bugs and you’ll want to make sure that you’re on the latest SP or CU. It’s definitely had a bit of a\u00a0checkered past. Here’s a list of issues and links to KB articles \/ bugs around this problem:<\/p>\n