SQL Server - Stored Procedure Parameter Sniffing

Parameter Sniffing

When SQL Server Database Engine compiles a stored procedure, it looks at the parameter value being passed and creates an execution plan based on the parameters. The process of looking at the parameter values of stored procedure when it is compiled is called Parameter Sniffing. The Parameter Sniffing is enabled by default.

To understand it, it is required to understand how a stored procedure is compiled and executed. When a stored procedure is executed first time, it creates an execution plan. The compiler goes through a number of steps in sequence as given below to execute a query.

1. Query
2. Parse
3. Optimize
4. Compile
5. Execute
6. Result

The query is parsed i.e. syntax of the query is checked. Then optimizer chooses the best plan best on execution cost. When the best plan is chosen, the code is compiled and then executed as per the execution plan. The execution plan is cached into memory by the SQL Server database engine.

When the query is executed next time, it is executed as per the saved execution plan created on first execution. There is no need to parse, optimize and compile the code in subsequent executions.

But there may be problem in execution plan if the parameter value is markedly different from the parameter value used first time because plan is based on the value of first time parameter value.

Use Database Properties dialog box to disable the Parameter Sniffing. It is in the Options > Database Scoped Configuration page.

With Recompile qualifier can be appended after last parameter before AS keyword in stored procedure definition. In this case, stored procedure is compiled every time is executed.

You can also set the new execution plan for a specific parameter value. This is given by using OPTION (OPTMIZE FOR (@variable=value)) in the SQL query of stored procedure.

You can also set the new execution plan for any parameter value. This is given by using OPTION (OPTMIZE FOR UNKNOWN) in the SQL query of stored procedure. Here a particular value is not passed but based on a guess value for all possible values, plan is created.