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

Optimize for a specific parameter

ALTER PROCEDURE Get_SalesOrderHeader   (