Tuesday, February 15, 2011

What is Parameter Sniffing, How it can affect the Performance, and What is the Solution

  • What is parameter sniffing?
  • How it can affect the performance.
  • What is solution of parameter sniffing?
Let us try to answer these questions,
Whenever a stored procedure is executed for the first time, its execution plan is created. An execution plan for a stored procedure is created the first time a stored procedure is executed. When the SQL SERVER Database engine compiles a stored procedure it looks at the parameters being passed to the stored procedure and then creates an execution plan based on these parameters. The process of looking at parameter values when compiling a stored procedure is called "Parameter sniffing". Sometimes Parameter sniffing can lead to inefficient execution plans, especially when a stored procedure is called with parameter values that have different cardinality.
Parameter sniffing can be considered a plus to the performance of the system, but in those cases where it's not, various mechanisms can be used to avoid it. One of the simplest is to use local variables. You can also use the OPTIMIZE FOR query hint or the WITH RECOMPILE query hint. In extreme cases you can force specific execution plans onto procedures.
We can also say that Parameter sniffing is a process that occurs when executing a stored procedure for the first time that allows SQL Server to build an effective query plan. Normally that is a good thing, but in some cases can actually hurt performance of the query.

According to the white paper
Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server 2005

Published in the Microsoft Site:
"Parameter sniffing" refers to a process whereby SQL Server's execution environment "sniffs" the current parameter values during compilation or recompilation, and passes it along to the query optimizer so that they can be used to generate potentially faster query execution plans. The word "current" refers to the parameter values present in the statement call that caused a compilation or a recompilation"

When a 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.
Before I show you with an example about parameter sniffing, you need to know that the query execution plan generated by the SQL Server depends on lot of factors; parameter sniffing is just one of them. So the execution plan I show here might be different from the execution plan that you will generate.
Let's look at the following code



GetCustOrders (@FirstCust int, @LastCust int)
SELECT * FROM Sales.SalesOrderHeader
WHERE CustomerID between @FirstCust and @LastCust;

This procedure GetCustOrders accepts two parameters @FirstCust int, @LastCust int . These parameters are passed in the where clause of the query to get the customer orders. Now look at the execution plan of the procedure by execution this procedure by passing different parameters.

USE AdventureWorks
EXEC GetCustOrders 1, 1000

I had used the statement
DBCC FREEPROCCACHE to clear the procedure cache so that query optimizer should make a new plan for this execution instead of using any existing one.
Look at the
Actual number of Rows 31465 and
Estimated number of Rows 31465
Here you can see that query optimizer performed a Clustered Index scan operation to get the records.
Now execute EXEC GetCustOrders 600,610 and see the execution plan
It uses the Clustered index scan for this operation as well, because this plan was present in the procedure cache.
Check the number of actual rows and estimated rows, 
Actual number of Rows 31465

Estimated number of Rows 31465

Note the in both cases the actual and estimated number of rows are same.

Now clear the procedure cache and execute the procedure with smaller values


EXEC GetCustOrders600,610
Exmaince the actual number of rows and estimated number of rows, they are different now, because optimizer created a new plane for this

Query optimizer performed the seek operation. This means that by passing the smaller range of values , you can get the Index Seek operation, depending on your execution.

If you execute the procedure with smaller values first and then execute the procedure with larger values. Then you can get Index seek for both of the executions.

The first compile of the Procedure generates the execution plan depending on the the parameters passed to the procedure and that plan is kept in the procedure cache for use of future executions of that procedure.

How to eliminate parameter sniffing?

There are three methods to avoid parameter sniffing,

  • By using local variables in the procedure
  • By using RECOMPILE Query Hint
  • Query hint OPTIMIZE FOR

By using Local variables
Parameter sniffing can be disabled by using the local variables in the stored procedure.

CREATE PROCEDURE GetCustOrders (@FirstCustint, @LastCustint)


DECLARE @FC int/td>
SET @FC = @FirstCust
SET @LC = @LastCust
SELECT * FROM Sales.SalesOrderHeader

You can create two local variables @FC AND @LC that takes the values from parameters of the stored procedure and then pass those values to the query. In this way 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.

This method of removing the parameter sniffing problem doesn’t mean you will get a best plan for each execution of the SP

By using Recompile
Creating a stored procedure that specifies the WITH RECOMPILE option in its definition indicates that SQL Server does not cache a plan for this stored procedure; the stored procedure is recompiled each time it is executed. Use the WITH RECOMPILE option when stored procedures take parameters whose values differ widely between executions of the stored procedure



CREATE PROCEDURE GetCustOrders(@FirstCust int,@LastCust int)



WHERECustomerIDbetween@FirstCustand@LastCust ;

By using Optimize For
Instructs the query optimizer to use statistical data instead of the initial values for all local variables when the query is compiled and optimized, including parameters created with forced parameterization

CREATE PROCEDUREGetCustOrders(@FirstCustint,@LastCustint)


WHERECustomerID between @FirstCustand @LastCust

You can read more about Query Hints from msdn

No comments:

Post a Comment

Share This