SQL Server查詢最佳化中的兩個選項

標籤：影響   變數   系統   flag   admin   維護   hint   嗅探   情況   

本文中，我們將介紹兩個SQL Server中的可用概念，它們是使用SQL Server時值得注意的技術。

1.        OPTIMIZE FOR Unknown

SQL Server 2005版本中增加了OPTIMIZE FOR提示（hint），允許DBA確定用於基數評估和最佳化的字面值。如果我們有一張資料分布傾斜的表，OPTIMIZE FOR能被用於最佳化為廣泛範圍參數值提供合理效能的通用值。當對所有參數值來說效能並非最好時，相比有時做尋找（seek，對於選擇性較好的參數值），有時做掃描（scan，對於選擇性一般的參數值），所有情境具備同樣的執行時間也許會更可取，這依賴於最初編譯期間傳入的參數值。

不幸的是，OPTIMIZE FOR僅允許字面值。如果變數為類似日期時間（datetime）或順序數（其本質隨時間而增長），那麼，確定的任何固定值不久將因變得過時而不得不修改該提示來確定一個新值。即使該參數範圍隨時間保持相對穩定，但提供字面值時你不得不實驗和發現一個足夠好的通用值，這有時是很難的或很費時間的。

最後，為OPTIMIZER FOR提供數值將通過改變使用該參數的謂詞基數評估而影響計劃的選擇。在OPTIMIZE FOR提示中，如果你提供了一個不存在或稀有值，那麼，你就減少了基數評估值，這將會影響成本和最終計劃的選擇。

如果你只想得到一個“平均”值而並不關心該值是什麼，OPTIMIZE FOR (@variable_name UNKNOWN)提示將導致最佳化器忽略影響基數評估的這個參數值。取而代之是用柱狀圖，基數評估將由密度、關鍵資訊或依賴謂詞的固定選擇性評估得出。這將導致一個並不需要DBA必須一直監視和改變參數值來維護一致效能的可預見評估。

文法變化將告訴最佳化器忽視所有參數值，這隻需確定OPTIMIZE FOR UNKNOWN並漏掉括弧和變數名。確定OPTIMIZE FOR將導致ParameterCompiledValue從showplan XML輸出中消失，正像參數嗅探（sniffing）沒有發生一樣。不管傳遞的參數，最終計劃將是一樣的，並且，也許會給出更加可預見的查詢效能。

 

2.        QUERYTRACEON 和QUERYRULEOFF

有些情境中，開發人員也許建議用追蹤旗標（trace flag）來避免查詢計劃或最佳化器問題。或者，他們也許發現禁用某個特定最佳化器規則會阻止特定問題的發生。一些追蹤旗標很常見，以至於難以預見開啟這些追蹤旗標是否能很好的解決所有查詢問題，或該問題是否只針對研究的特定查詢。類似的，大多數最佳化器規則並非本身不好，整個系統範圍內禁用該規則可能會導致其他方面的效能退化。

SQL Server 2008中，可以在特定查詢運行期間開啟某個追蹤旗標，或通過如下未被歸檔QUERYTRACEON或QUERYRULEOFF提示僅在查詢編譯期間禁用某個最佳化器規則。

select @v_test=c1from t1 where c1=2 option(recompile,querytraceon 2389);

select @v_test=c1from t1 where c1=2 option(recompile,queryruleoff OmitMyidx);

上述第二個語句顯示的文法也許會導致“no plan”錯誤。預先未與開發人員討論以確保完全理解該規則及禁用可能帶來的後果，就不應該使用QUERYRULEOFF。資料庫屬主通常擁有建立一個計劃指導（plan guide）所需的足夠許可權，而用QUERYTRACEON/QUERYRULEOFF提示建立一個計劃指導則需要sysadmin許可權，因為改變這些設定也許有系統而非資料庫範圍的含義。

結論

最後，清楚你的環境中何時使用這些查詢最佳化或查詢調優技術很重要，請在使用這些技術前，分析具體情況並進行足夠的測試。.

SQL Server查詢最佳化中的兩個選項