THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Benjamin Nevarez

The Query Optimizer and Parameter Sniffing

 

As we all know, the SQL Server Query Optimizer uses statistics to estimate the cardinality and selectivity of predicates of a query to produce an optimal execution plan. The Query Optimizer accomplishes this by first inspecting the values of the query parameters. This behavior is called parameter sniffing and it is a good thing. Getting an execution plan tailored to the current parameters of a query improves the performance of your applications.

 

We also know that the plan cache can store these execution plans so they can be reused the next time the same query needs to be executed again. This saves optimization time and CPU resources as the query does not need to be compiled again.

 

However, although the Query Optimizer and the plan cache work fine together most of the times, occasionally some performance problems can appear. Since the Query Optimizer may produce different execution plans for the same query, depending on its parameters, caching and reusing only one of these plans may be a performance issue for some other instance of this query that could benefit from a better plan.

 

This is a known problem with queries using explicit parameterization like in, for example, stored procedures. So let us see an example of what the problem is and a few recommendations on how to fix it.

 

1) The problem

 

Let us write a simple stored procedure using the Sales.SalesOrderDetail table on the AdventureWorks database

 

create procedure test (@pid int)

as

select * from Sales.SalesOrderDetail

where ProductID = @pid

Run this to display the amount of disk activity generated by the query

 

set statistics io on

and execute the stored procedure

 

exec test @pid = 897

The Query Optimizer estimates that only a few records will be returned by this query and decides to use an existing index, so if you look at the actual execution plan you will see an Index Seek and a Key Lookup operators. The I/O information will be similar to this

 

logical reads 10, physical reads 0, read-ahead reads 0

 

Now clear the plan cache to remove this plan and run the stored procedure again using a new parameter (Note: Be careful not to clear the plan cache of a production environment)

 

dbcc freeproccache

exec test @pid = 870

This time you will get 4,688 rows and the execution plan will show a Clustered Index Scan. The I/O information will be similar to this

 

logical reads 1240, physical reads 0, read-ahead reads 0

 

In this example each execution created its own optimal execution plan. Now see what happen when the plan cache is not cleared before the second execution so they both use the same cached plan

 

dbcc freeproccache

exec test @pid = 870

exec test @pid = 897

This time the Query Optimizer will compile the first execution of the stored procedure and will create an optimal execution plan for the value 870. This will use a Clustered Index Scan and around 1,240 logical reads. Since this plan is cached, it will also be used for the second execution, using the value 897 and it will also show a Clustered Index Scan and around 1,240 logical reads. This second execution is using 124 times more reads than its optimal plan, as shown previously.

 

You can try the other combination as well, clearing the plan cache, and running these two executions of the stored procedure, but this time using the value 897 on the first one and 870 on the second one.

 

2) Optimize for a typical parameter

 

There might be cases when most of the executions of a stored procedure use the same execution plan and/or you want to avoid the optimization cost. For these cases you can use the OPTIMIZE FOR hint. Use this hint when an optimal plan is generated for the majority of values used for the parameter. Only the few executions using an atypical parameter will not have an optimal plan.

 

Suppose that almost all of the executions of our stored procedure would benefit from the previous plan using an Index Seek and a Key Lookup.  You could write the stored procedure this way

 

alter procedure test (@pid int)

as

select * from Sales.SalesOrderDetail

where ProductID = @pid

option (optimize for (@pid = 897))

When you run the stored procedure it will be optimized for the value 897, no matter what parameter value was specified for the execution. Test the following case

 

exec test @pid = 870

You can find this in the XML plan

 

<ParameterList>

    <ColumnReference Column="@pid" ParameterCompiledValue="(897)" ParameterRuntimeValue="(870)" />

</ParameterList>

This clearly shows which value was used during optimization and which one during execution. In this case the stored procedure is optimized only once, and the plan is stored on the plan cache and reused as many times as needed. The benefit of using this hint is that you have total control on which plan is stored on the plan cache.

 

3) Optimize on every execution

 

If you want the best performance for every query the solution might be to optimize for every execution. You will get an optimal plan on every execution but will pay for the optimization cost. To do this use the RECOMPILE hint as shown here.

 

alter procedure test (@pid int)

as

select * from Sales.SalesOrderDetail

where ProductID = @pid

option (recompile)

The XML plan for this execution

 

exec test @pid = 897

will show

 

<ParameterList>

    <ColumnReference Column="@pid" ParameterCompiledValue="(897)" ParameterRuntimeValue="(897)" />

</ParameterList>

Some other solution that has been traditionally implemented before has been using local variables but usually this is not a good idea. By doing this you are not only disabling parameter sniffing but also disabling the choice of the Query Optimizer to use the statistics histogram to find an optimal plan for the query. This solution will use the same execution plan for all the executions but may not be the optimal plan for any of them.

Published Thursday, August 27, 2009 8:42 PM by Ben Nevarez
New Comments to this post are disabled

About Ben Nevarez

Benjamin Nevarez is a SQL Server MVP and independent consultant based in Los Angeles, California who specializes in SQL Server query tuning and optimization. He is the author of "SQL Server 2014 Query Tuning & Optimization" and "Inside the SQL Server Query Optimizer" and co-author of "SQL Server 2012 Internals". With more than 20 years of experience in relational databases, Benjamin has also been a speaker at many SQL Server conferences, including the PASS Summit, SQL Server Connections and SQLBits. Benjamin’s blog can be found at http://www.benjaminnevarez.com and he can also be reached by e-mail at admin at benjaminnevarez dot com and on twitter at @BenjaminNevarez.
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement