SQL Server Parameter sniffing and optimize for introduction

Parameter sniffing is a function of generating an execution plan based on the parameters passed in when SQL Server creates the execution plan for a stored procedure, in layman's terms, the execution plan is generated based on the first parameter for stored procedure, but stored The execution plan generated by the first parameter of the procedure is not necessarily optimal, and when the execution plan is reused for subsequent arguments, the original execution plan cannot respond efficiently to this query, resulting in inefficient query performance.

For problems caused by parameter sniffing, you can use the re-editing stored procedure, or use OPTIMIZE for hint to avoid.

The use of Optimize for query hint is to generate the best execution plan using this hint (during the query optimization phase), but this value is not queried (during the query execution phase).

For example, the following statement

 declare   @ID  int  set   @ID  =  567  select  *  from   Dbo.dt_test  where  id>   @ID  option  (OPTIMIZE for  ( @ID  =  541 ) 

SQL Server uses @id=541 to generate the execution plan during the query optimization phase, but when the query executes phase, @id=567 is used as the criteria for the query to get the result set.
If the optimize for query is not applicable, Hint,sql server uses @id-567 to generate the execution plan, using @id=567 as the query criteria to get the result set.

MSDN's explanation for query hint:

OPTIMIZE for ( @variable_name {UNKNOWN | = literal_constant} [ , ... N])

Instructs the query optimizer to use a particular value for a local variable when the query is compiled and optimized. The value is used only during query optimization, and not during query execution.

@variable_name

is the name of a local variable used in a query, to which a value could be assigned for use with the OPTIMIZE for query hint .

UNKNOWN

Specifies that the query optimizer use statistical data instead of the initial value to determine the value for a local VA riable during query optimization.

Literal_constant

is a literal constant value to be assigned@variable_name for use with the OPTIMIZE for query hint.Literal_constant is used-during query optimization, and not as the value of @variable_name during query execution. literal_constant can be any of the SQL Server system data types represented by any of the available literal constants. ">literal_constant can be is of any SQL Server system data Type the can be expressed as a literal co Nstant. literal_constant must be implicitly convertible to The data type referenced by the @variable_name. ">the data type of literal_constant must is implicitly convertible to the data type that @variable_name references in the query.

OPTIMIZE for can counteract the default parameter detection behavior of the optimizer or can is used when you create plan Guides. For more information, see Recompile a Stored Procedure.

OPTIMIZE for UNKNOWN

If OPTIMIZE for @variable_name = literal_constant and OPTIMIZE for UNKNOWN is used in the same query hint, the query Optimizer'll use the literal_constant, that's specified for a specific value and UNKNOWN for the remaining variable Values. The values is used only during query optimization, and not during query execution.

Reference article:

Http://www.dotblogs.com.tw/ricochen/archive/2012/04/23/71725.aspx

https://msdn.microsoft.com/en-us/library/ms181714 (sql.100). aspx

SQL Server Parameter sniffing and optimize for introduction