SQL Server : Parameter Sniffing

Two days back one of my colleque came to me and started complaining about the performance of an Stored Procedure. Her complaint was one stored procedure is taking long time from the application but completing in two seconds in SSMS.I told her it might be due to parameter sniffing but she was not ready to accept that because store procedure is running using the same parameter in the application and SSMS. Let us try to understand the parameter sniffing and various solution for parameter sniffing.

Understand the parameter sniffing

SQL Server use the statistics to estimate the cardinality of a predicate(where condition) and use that information to produce an optimal execution plan for the current parameter.This execution plan will be cached in the procedure cache and used for subsequent execution of the same parameterized query even with different parameter values.This behavior is called parameter sniffing and it is not bad always. This help us to save the optimization time as the query does not need to be compiled again. The problem occurs when the plan generated for the first execution is not optimal for the subsequent execution.For example let us assume that we have stored procedure which returns the details of the customer based on the input range.In the first execution we have passed parameters as 1 and 5000 and the execution plan might perform an index scan on the customer table. In the next execution of the same procedure will use the same plan even if you passed parameters 1 and 10.The optimal plan might be a index seek and the table scan due to the parameter sniffing will cause for delay in response and excess usage of resources. Many people have a misconception that parameter sniffing will happen only with stored procedure but it can happen with parameterized query also.

Let us see execution plan of the below two statements

SELECT * FROM Sales.SalesOrderHeader WHERE CustomerID BETWEEN 11000  AND 30118
GO
SELECT * FROM Sales.SalesOrderHeader WHERE CustomerID BETWEEN 11000  AND 11002

The first query is done withe clustered index scan and the second one is done with index seek with key look up operation. These are the optimal execution plan. Let us see how it will work if these statement are converted into a procedure/parameterized query.

EXEC sp_executesql N'SELECT * FROM Sales.SalesOrderHeader WHERE CustomerID between @Start and @End;',N'@Start INT,@End INT',@Start=11000,@End=30118

GO
EXEC sp_executesql N'SELECT * FROM Sales.SalesOrderHeader WHERE CustomerID between @Start and @End;',N'@Start INT,@End INT',@Start=11000,@End=11002
GO

Surprisingly the execution plans of both statements are same! Right click on the left most of operator of the second execution plan and select properties.we can see the below information.



We can see that the parameter compiled values are same as the parameter of the first statement. You can see the same information in the XML execution plan also as given below.

Plan Re-usability

As we discusses the earlier, query optimization and plan generation are costly operations.To avoid this cost , SQL server will try to keep the generated execution plans in plan cache and reuse the same in subsequent execution.However, It is not possible to use the same plan , if the connection has different SET options.It will force to generate new plan instead of using the cached plan and this plan will be reused by all connection having the same SET options.The following SET options are plan-reuse-affecting:

ANSI_NULL_DFLT_OFF
DATEFIRST
ANSI_NULL_DFLT_ON
DATEFORMAT
ANSI_NULLS
FORCEPLAN
ANSI_PADDING
LANGUAGE
ANSI_WARNINGS
NO_BROWSETABLE
ARITHABORT
NUMERIC_ROUNDABORT
CONCAT_NULL_YIELDS_NULL
QUOTED_IDENTIFIER

The above SET options are plan-reuse-affecting because SQL Server performs "constant folding" (evaluating a constant expression at compile time to enable some optimizations) and because settings of these options affects the results of such expressions.Let us see an example.

CREATE PROCEDURE Get_SalesOrderHeader   ( 

@FromCustid INT,
@ToCustid INT  )  AS
SELECT * FROM Sales.SalesOrderHeader WHERE CustomerID BETWEEN @FromCustid AND @ToCustid  

Run this procedure from one of the SSMS window 

SET ARITHABORT OFF
EXEC Get_SalesOrderHeader  11000,11002
GO

Run below query to find out the plan details which will return one row with usecounts value 1



SELECT OBJECT_NAME(CONVERT(INT,PvtAttr.objectid)),plan_handle, usecounts, PvtAttr.set_optionsFROM (
    SELECT plan_handle, usecounts,f.value,f.attribute
    FROM sys.dm_exec_cached_plans
        OUTER APPLY sys.dm_exec_plan_attributes(plan_handle) f
    WHERE cacheobjtype = 'Compiled Plan') AS PlanAttr
PIVOT (MAX(PlanAttr.value) FOR PlanAttr.attribute IN ("set_options" "objectid") AS PvtAttrWHERE PvtAttr.objectid = OBJECT_ID('dbo.get_SalesOrderHeader')




In another SSMS window, execute the procedure in same way and run the query to find the plan details which will again will return only one row with usecounts value 2.In one of the SSMS window change the ARITHABORT setting to ON and run the procedure.Run the query to get the plan details which will return two rows with different set_options value.ADO.NET and SSMS have different default SET options and it might cause to use two different execution plan by the application and SSMS.

Possible Solutions for Parameter Sniffing

If you feel that  parameter sniffing affecting the performance of a procedure, you have a few choices.

Optimize for a specific parameter

This can be used , if you feel that most of the execution of the procedure can be benefited form execution plan of a specific parameters. For example in our case , let us assume that the procedure is going return maximum of two or three customers in most of the execution. Then you can create procedure as given below

ALTER PROCEDURE Get_SalesOrderHeader   ( 

@FromCustid INT, @ToCustid INT  )  AS
SELECT * FROM Sales.SalesOrderHeader WHERE CustomerID BETWEEN @FromCustid AND @ToCustid  OPTION (OPTIMIZE FOR (@FromCustid = 11000,@ToCustid = 11002))

Recompile for all execution

This option will force to recompile the procedure for every execution and generate optimal plan for the current parameter. It has a overhead of recompilation .If the procedure has many statements, the cost of recompilation will increase.This can be achieved in two ways.

ALTER PROCEDURE Get_SalesOrderHeader  
( @FromCustid INT, @ToCustid INT  )  
AS
SELECT * FROM Sales.SalesOrderHeader WHERE CustomerID BETWEEN @FromCustid AND @ToCustid  OPTION (RECOMPILE)
GO
ALTER PROCEDURE Get_SalesOrderHeader   
( @FromCustid INT, @ToCustid INT  )  WITH RECOMPILE
AS
SELECT * FROM Sales.SalesOrderHeader WHERE CustomerID BETWEEN @FromCustid AND @ToCustid  
An interesting difference between two of this approach is : WITH RECOMPILE , the plan is never put into the plan cache, whereas this happens with OPTION (RECOMPILE) and it use the plan cache in following situations.

1. The procedure is called with a very high frequency, and the compilation overhead hurts the system.
2. The query is very complex and the compilation time has a noticeable negative impact on the response time.

Local Variable or option for unknown

The traditional way of handling the parameter sniffing is assigning the parameter value to a local variables and use the local variable. The option for unknown also works in the same way. When the query optimizer knows the parameter value, it can use the statistical histogram to estimate the number of records that can be returned by the query and can generate the best plan based on the histogram of the statistics .When the parameters values are assigned to the local variable , optimizer can not use histogram instead it uses the density vector of the statistics. I will explain this in detail in my my next post.

This can be implemented as given below:

ALTER PROCEDURE Get_SalesOrderHeader   ( @FromCustid INT, @ToCustid INT  )  AS
SELECT * FROM Sales.SalesOrderHeader WHERE CustomerID BETWEEN @FromCustid AND @ToCustid  OPTION (OPTIMIZE FOR UNKNOWN)

GO

ALTER PROCEDURE Get_SalesOrderHeader   ( @FromCustid INT, @ToCustid INT  )AS
DECLARE
DECLARE  @L_FromCustid INT
DECLARE  @L_ToCustid INT
SET @L_FromCustid   =@FromCustidSET @L_ToCustid     =@ToCustid SELECT * FROM Sales.SalesOrderHeader WHERE CustomerID BETWEEN @L_FromCustid AND @L_ToCustid  

Apart from this we can implement a decision tree procedure where it will call two separate procedure based on difference between two parameter value.For example one for fetching less than 50 customer and other one for fetching more than 50 customers.

Thank you for reading this post.If you liked this post, do like my page on Facebook : http://www.facebook.com/practicalSqlDba