T-SQL Best Practices - Parameter Sniffing

September 30, 2009

This article is an extension of my T-SQL best practices series. In this article, I’ll be exploring how SQL Server optimizes and caches stored procedure execution plans based on the parameters that are passed the first time a stored procedure (SP) is executed. This discussion will explore a concept called “Parameter Sniffing” and how this can lead to execution plans that are not optimal for all calls to an SP.

What is Parameter Sniffing?

When an SP is not in the procedure cache, when it is executed the query optimizer needs to compile the SP to create an execution plan. In order to do this the query optimizer needs to look at the parameters that are passed and the body of the SP to determine the best method to go about processing the SP. When the query optimizer looks at the SP’s parameters, to help determine how to optimize the execution of the SP, it is known as parameter sniffing.

Once the query optimizer has sniffed the parameters and determined the best approach for processing the SP, it caches the execution plan in the procedure cache. All subsequent executions of the SP re-use the execution plan from the procedure cache regardless if different parameters are passed. The potential problem with this approach is the parameters that were used when the plan was cached might not produce an optimal plan for all execution of the SP, especially those that have significantly different set of records returned depending on the parameters passed. For instance, if you passed parameters that required a large number of records to be read, the plan might decide a table or index scan would be the most efficient method to process the SP. Then if the same SP was called with a different set of parameters that would only return a specific record, it would used the cached execution plan and perform an table or index scan operation to resolve it’s query, even if a index seek operation would be more efficient in returning the results for the second execution of the SP.

If you have an SP that sometimes processes quickly, and other times processes slowly with different sets of parameters, then possibly parameter sniffing is causing your procedures to have varying execution times. In this article we will look at different methods to write your SPs to help the query optimizer in picking a good execution plan most of the time.

Review the Problem Caused by Parameter Sniffing

Before we look into how to write your SPs to control the issues related to parameter sniffing, let’s look at the issues caused by it first. To do that let’s review the following code:

USE AdventureWorks
GO
CREATE PROC GetCustOrders (@FirstCust int, @LastCust int) 
AS 
SELECT * FROM Sales.SalesOrderHeader
WHERE CustomerID between @FirstCust and @LastCust;

This code accepts two parameters. These two parameters are used in a WHERE clause to determine which SalesOrderHeader records to return. Now, let’s look at the execution plan for this SP when we call it with two different sets of parameters. First, let’s call it with the following code:

USE AdventureWorks
GO
DBCC FREEPROCCACHE
EXEC GetCustOrders 1,1000

When I execute the above code, I get the following execution plan:

Here, you can see that this execution of the GetCustomerOrder SP performed a Clustered Index Scan operation when I called this procedure with a range of CustomerID’s from 1 to 1000. Note, I freed the procedure cache using the “DBCC FREEPROCCACHE” statement to make sure that the query optimizer compiled the GetCustOrders SP based on the parameters I passed.

Now, I will execute the same SP with a smaller range of CustomerID’s and see what kind of execution plan I get. Here is the code I will be executing:

USE AdventureWorks
GO
DBCC FREEPROCCACHE
EXEC GetCustOrders 600,610

When I execute this code, I get the following execution plan:

This time I get a different execution plan. Now, you can see that when using a smaller range of CustomerID’s (600-610), I get an Index Seek operation. Depending on which execution of this SP was the first to be execute it, would compile and cache the execution plan for all subsequent executions of this SP. To verify this let’s run the following code and review the execution plans:

USE AdventureWorks
GO
DBCC FREEPROCCACHE
EXEC GetCustOrders 1,1000
GO
EXEC GetCustOrders 600,610

Here is the execution plan for the above T-SQL batch:

By looking at this code, you can see that the second execution of the GetCustOrder SP now performs a Clustered Index Scan operation to find all the customer orders from a small range of CustomerID’s. This occurs because the query optimizer only sniffed the parameters for the first execution of the GetCustOrder, which had a large range, and then cached that execution plan. The cached execution plan was then used for the second execution of the GetCustOrder SP.

The first compile of an SP creates the execution plan based on the parameters passed, and then this plan is stored in the procedure cache for the current and all future executions of the same SP. The last code segment above demonstrated how this could cause our second execution of GetCustOrder to use a less efficient execution plan--in this case, a Clustered Index Scan operation instead of an Index Seek operation. Let’s look at ways to overcome this problem.

Eliminating the Parameter Sniffing Problem by Disabling Parameter Sniffing

In the prior example, I demonstrated how the first execution of an SP sniffed the parameters and then built an execution plan that was optimized for that set of parameters. This can lead to poor execution plans for any call that the SP uses that have a different set of parameters that might lead to a different execution plan. In my demonstrations above I showed that when a large range of CustomerID’s (1-1000) was sent to the GetCustOrder SP it used an index scan operation, but if a small range (600-610) was sent the optimal plan was an index seek operation. However, because SQL Server tries to minimize compiles of the SP, my small range of customerID’s used an index scan operation. If you have a wide variation in parameters that might be passed to an SP, you can eliminate the parameter sniffing problems by disabling parameter sniffing. You do this by coding your SP a specific way. Below I have rewritten my SP to eliminate parameters sniffing:

CREATE PROC GetCustOrders (@FirstCust int, @LastCust int) 
AS
DECLARE @FC int
DECLARE @LC int
SET @FC = @FirstCust
SET @LC = @LastCust
SELECT * FROM Sales.SalesOrderHeader
WHERE CustomerID BETWEEN @FC AND @LC

Here, can see I have declared two local variables (@FC and @LC) and then populated them with the values of the parameters passed to my SP. By doing this, the actual values of the parameters are no longer contained in the BETWEEN clause in the SELECT statement, instead only those local variables are present. Because of this small change, the query optimizer looks at all the statistics related to objects in my query and determines on average what might be the best execution plan to use based on the statistics.

Still, this method of eliminating the parameter sniffing problem doesn’t mean you will get an optimal plan for each execution of the SP. You still only get one execution plan stored in the procedure cache, which will be used for all executions of the SP. Although that one execution plan on average will perform optimal if you call the SP many times with many different parameter values. If you really want to create different execution plans based on the parameters passed then you will need to uses a different approach.

Resolving the Parameter Sniffing Problem Using a Decision Tree SP

If you have an SP that is called with a number of different parameter values, where depending on the parameters passed they get different execution plans, you can solve the parameter sniffing problem by creating a decision tree SP. The decision tree approach has a single SP that is called, which decides which SP to call based on the parameters passed. This allows more than one SP to support your varying parameter values and allows for a more optimize plan to be used based on the parameters passed. Let me show you an example of a decision tree approach to resolving the parameter sniffing problem.

Let’s use the same situation as the above examples, where I want to return SalesOrderHeader records based on a range of CustomerIDs. Instead of the GetCustOrders SP selecting the order header records, it will instead determine the difference in range and then call a different SP based on whether the range of CustomerID’s is small or large. Let’s look at the code for the following three SPs:

CREATE PROC GetCustOrders (@FirstCust int, @LastCust int) 
AS 
IF @LastCust - @FirstCust < 100
	EXEC GetCustOrdersNarrow @FirstCust, @LastCust
ELSE
	EXEC GetCustOrdersWide @FirstCust, @LastCust 
GO
-- Proc for Large Range of Customers
CREATE PROC GetCustOrdersWide (@FirstCust int, @LastCust int) 
AS 
SELECT * FROM Sales.SalesOrderHeader
WHERE CustomerID BETWEEN @FirstCust AND @LastCust  
GO
-- Proc for Small Range of Customers
CREATE PROC GetCustOrdersNarrow (@FirstCust int, @LastCust int) 
AS 
SELECT * FROM Sales.SalesOrderHeader
WHERE CustomerID BETWEEN @FirstCust AND @LastCust  
GO

Here, you can see I have created three different SPs. The first SP (GetCustOrders) is my decision tree SP. This SP determines if the range of CustomerIDs is less than 100. If it is, it calls GetCustOrdersNarrow otherwise it calls GetCustOrdersWide. These two SPs are exactly the same. I’ll execute the following code to determine what execution plans are generated based on the parameters passed using this code:

DBCC FREEPROCCACHE
EXEC GetCustOrders 1,1000
EXEC GetCustOrders 600,610

Here are the two different execution plans produced by the two different EXEC statements:

As you can see, the first execution that had a CustomerID range greater than 100 used a Clustered Index Scan operation to resolve its execution. Whereas the second execution that had a CustomerID range of less than 100 used an Index Seek operation followed by a Key Lookup to resolve this smaller range query. So by building the decision tree approach, each execution with different parameters was resolved with a more efficient execution plan.

Controlling Parameter Sniffing

The query optimizer uses parameter sniffing to help determine what execution plan should be used to optimize execution of an SP. This process, as you can see, sometime causes your SP to execute a plan that was optimized based on a different set of parameters because of plan caching. I demonstrated a way to disable parameter sniffing so SQL Server uses the statistics to determine on average what would be the optimal plan. In addition, I showed you how to create a decision tree SP to help make sure you have different cached plans for different parameter values ranges. If you find your queries are running slow sometimes with different parameter values then you might want to determine if parameter sniffing is causing your performance issues.

» See All Articles by Columnist Gregory A. Larsen