Sql Server Parameter Sniffing 和 optimize for 介紹

標籤：

Parameter sniffing是Sql Server 建立預存程序的執行計畫時，根據傳入的參數進行預估產生執行計畫的一個功能，通俗的說，就是根據第一個參數為stored procedure產生執行計畫，但是Stored Procedure的第一個參數產生的執行計畫並不一定是最優的，當後續傳參時重用執行計畫時，原有的執行計畫無法高效響應本次查詢，導致查詢效能低效。

 

對於參數嗅探導致的問題，可以使用重新編輯stored procedure，或使用optimize for hint來避免。

Optimize for 查詢hint的用法是：使用該提示（在查詢最佳化階段）產生最佳的執行計畫，但是（在查詢執行階段）不適用該值進行查詢。

例如以下語句

declare @ID intset @ID=567select * from dbo.dt_testwhere id>@IDoption(OPTIMIZE for(@ID=541))

Sql Server在查詢最佳化階段，使用@ID=541來產生執行計畫，但是當查詢執行階段，使用@ID=567來作為查詢的條件來擷取結果集。
如果不適用 optimize for 查詢hint，sql server 使用@ID-567來產生執行計畫，使用@ID=567來作為查詢條件來擷取結果集。

 

MSDN 對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 may 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 variable 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 only during query optimization, and not as the value of @variable_name during query execution. literal_constant can be of any SQL Server system data type that can be expressed as a literal constant. The data type of literal_constant must be 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 be used when you create plan guides. For more information, see Recompile a Stored Procedure.

OPTIMIZE FOR UNKNOWN              

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.                 

If OPTIMIZE FOR @variable_name = literal_constant and OPTIMIZE FOR UNKNOWN are used in the same query hint, the query optimizer will use the literal_constant that is specified for a specific value and UNKNOWN for the remaining variable values. The values are used only during query optimization, and not during query execution.

 

 

參考文章：

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 和 optimize for 介紹