關於SQL Server的查詢提示OPTION (OPTIMIZE FOR UNKNOWN) ，它是解決參數嗅探的方法之一。 而且對應的SQL語句會緩存，不用每次都重編譯。關鍵在於它的執行計劃的準確度問題， 最近在優化的時候，和同事對於這個查詢提示（Query Hint）有一點分歧，遂動手實驗驗證、... ...
關於SQL Server的查詢提示OPTION (OPTIMIZE FOR UNKNOWN) ，它是解決參數嗅探的方法之一。 而且對應的SQL語句會緩存，不用每次都重編譯。關鍵在於它的執行計劃的準確度問題， 最近在優化的時候，和同事對於這個查詢提示（Query Hint）有一點分歧，遂動手實驗驗證、總結了一些東西。
關於提示OPTION (OPTIMIZE FOR UNKNOWN)，它會利用統計數據和標準演算法生成一個折中、穩定的執行計劃，但是它是無法利用直方圖（histogram）信息來生成執行計劃。官方文檔的介紹如下：
OPTIMIZE FOR 編譯和優化查詢時提示查詢優化器對本地變數使用特定值。僅在查詢優化期間使用該值，在查詢執行期間不使用該值。
UNKNOWN
指定查詢優化器在查詢優化期間使用統計數據而不是初始值來確定局部變數的值。OPTIMIZE FOR 可以抵消優化器的預設參數檢測行為，也可在創建計劃指南時使用
OPTIMIZE FOR UNKNOWN
指示查詢優化器在查詢已經過編譯和優化時為所有局部變數使用統計數據而不是初始值，包括使用強制參數化創建的參數。有關強制參數化的詳細信息，請參閱強制參數化。
如果在同一查詢提示中使用 OPTIMIZE FOR @variable\_name = literal_constant 和 OPTIMIZE FOR UNKNOWN，則查詢優化器將對特定的值使用指定的 literal_constant，而對其餘變數使用 UNKNOWN。這些值僅用於查詢優化期間，而不會用於查詢執行期間。
OPTIMIZE FOR UNKNOWN是否會用直方圖數據呢？ 不會，OPTIMIZE FOR UNKNOWN只會用簡單的統計數據。我們以how-optimize-for-unknown-works這篇博客中的例子來演示一下， 下麵測試環境為SQL Server 2014，資料庫為AdventureWorks2014
CREATE PROCEDURE test (@pid int)
ASSELECT * FROM [Sales].[SalesOrderDetail]
WHERE ProductID = @pid OPTION (OPTIMIZE FOR UNKNOWN);
為了消除統計信息不准確會幹擾測試結果，我們手工更新一下統計信息。
UPDATE STATISTICS [Sales].[SalesOrderDetail] WITH FULLSCAN;
我們在SSMS裡面點擊“包含實際執行計劃”選項，然後測試執行該存儲過程，如下截圖所示： 執行計劃居然走聚集索引掃描
EXEC test @pid=709
Filter裡面過濾的記錄為456.079，而實際上ProductID=709的記錄有188條，那麼優化器是怎麼估計判斷記錄數為456.709的呢？
其實優化器是這樣來估計的：它使用ProductID列的密度(Density)* Rows來計算的
SELECT 0.003759399 *121317 ~= 456.079008483 ~= 456.079
而ProductID列的密度(Density)的計算是這樣來的:
ProductID的值有266個，可以用下麵SQL獲取ProductID的值個數
SELECT COUNT(DISTINCT ProductID) FROM Sales.SalesOrderDetail
SELECT 1.0/266 ~= 0.003759
然後你可以使用任意不同的參數測試，例如707、712......, 你會發現使用查詢提示OPTION (OPTIMIZE FOR UNKNOWN)後，優化器會總是使用相同的執行計劃。也就是說這個查詢提示生成的執行計劃是一個“折中的執行計劃” ，對於數據分佈傾斜的比較厲害（數據分佈極度不均衡）的情況下，是極度不建議使用查詢提示OPTION (OPTIMIZE FOR UNKNOWN)的。
本人曾經一度對使用OPTION（RECOMPILE）還是OPTION (OPTIMIZE FOR UNKNOWN)感到困惑和極度難以取捨，後面總結了一下：
1：執行不頻繁的存儲過程，使用OPTION（RECOMPILE）要優先與OPTION (OPTIMIZE FOR UNKNOWN)
2：執行頻繁的存儲過程，使用OPTION (OPTIMIZE FOR UNKNOWN)要優先於OPTION（RECOMPILE）
3：數據分佈傾斜的厲害的情況下，優先使用OPTION（RECOMPILE）
4: 使用OPTION (OPTIMIZE FOR UNKNOWN)會生成一個穩定、統一的執行計劃，如果這個執行計劃的效率基本能滿足用戶需求，那麼優先使用OPTION (OPTIMIZE FOR UNKNOWN)
參考資料：
https://docs.microsoft.com/zh-cn/previous-versions/sql/sql-server-2008/ms181714(v=sql.100)
http://www.benjaminnevarez.com/2010/06/how-optimize-for-unknown-works/
https://blogs.msdn.microsoft.com/sqlprogrammability/2008/11/26/optimize-for-unknown-a-little-known-sql-server-2008-feature/
