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.
