The Query Optimizer and Parameter Sniffing

Ben Nevarez — Fri, 28 Aug 2009 02:42:00 GMT

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)

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)

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>

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)

select * from Sales.SalesOrderDetail

where ProductID = @pid

option (recompile)

The XML plan for this execution

exec test @pid = 897

will show

</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.

Search results matching tags 'Performance', 'Optimization', and 'Parameter Sniffing'

The Query Optimizer and Parameter Sniffing