SQL Server 效能調整 - Optimizer Hint 的使用

下列的文章是從SQL Server Performance的網站轉載而來，剛好與我的另外一篇文章 [你是否需要 SQL Server Query Hint ?] 有相關，所以特別整理在此，藉以提供參考。

        在很多的案例上，SQL Server 的查詢優化器將正確的評估一個查詢和執行最佳化的可能，但是在某些情況下可能查詢優化器會產生一些不好的執行計畫，造成查詢效能的不佳。當你分別的去檢查每一個查詢時，你可以透過優化提示 (Optimizer Hint) 改寫查詢計畫，而Hint的種類上分成下列五種：

1. Table Hints: 在查詢時指定特定的索引。
2. Join Hints: 指定兩個表格結合時的策略。
3. Query Hints: 提示的使用將會影響GROUP BY 和 UNION 的運作。
4. Lock Hints : 使用上去避免不好的鎖定。
5. View Hints: 指定 Views 的 Indexs 。

        在本質上，hints可以覆寫查詢優化推薦器。如果查詢優化器有錯誤時，使用hints是非常有幫助的。但如果你撰寫的 hints 有錯誤時，他將持續錯誤到你發現到後改變他為止，否則他會同樣的持續影響效能。

        一般的來說，hints 應該是可以避免少用。因為查詢優化器是非常聰明(大部份的時間)，而且產生的執行計畫是最好的。所以我們必須確認所認知的部份，不要在不明確的情況下進行使用。當查詢優化器錯誤而且是我們確認的情況下，就可以透過 hints 來改善，進行最好的測試去並找到更好的方法。

        如果你想像一個 hints 可能可以優化你的查詢，但是在使用前最好確認下列的步驟，，因為下列的其中一個將可能是實際問題：

更新相關表格的statistics。

• 如果有問題的查詢是透過 stored procedure，請重新編譯，並且重新執行確認執行的狀況是否有變好。
• 重新檢示查詢參數是否合適，並且嘗試重新改寫藉以改善。
• 重新檢示目前的索引，如果需要時可以嘗試進行更動。
• 如果你已有進行以上的動作，查詢仍然無法如同你的預期，那你可以考慮去使用 hints。

        另外一個問題，使用 hints 在一個確信的地方是可以理解的，但如果情境改變時，可能 hints 會變得失效。所以如果你決定去使用一個 hints，你需要去建立一些處理記錄提醒自已定期去重新檢查 hints 的效能。如同資料或程式碼變更，都有可能造成 hints 的效能影響。如果沒有注意反而最終會讓hints降低效能而非提升。

*****
Hints 在大部份的時間上造成效能的降低影響比實質幫助上還多，舉例來說，你接管一個database，其中使用到許多的 hints，此時你需要先行了解到 hints 對效能的影響是好還是壞。方法上可以測試各個查詢在有 hints 與沒有的情況下，執行時間的表現。如果只有少數幾個 hints 時，在測試上可能不是個問題。但是如果數量非常多的時候，比如說幾千個時，有沒有什麼好的方法來重新確認全部的 hints 呢？

依據你的情境，有一個粗陋的方式可以關閉所有的 hints 針對單一使用者連線，或是全部SQL Server的連線。透過這個方法，你可以不需修改任何的程式即可。

DBCC TRACEON(8602)
上述的命令是關閉目前的連接中所有的index hints。所以執行此命令之後，任何的Transact-SQL將會忽略任何的index hints.

DBCC TRACEON(8755)
上述的命令是關閉目前連接中所有的locking hints。所以執行此命令之後，任何的Transact-SQL將會忽略任何的locking hints.

DBCC TRACEON(8722)
上述的命令是關閉目前連接中所有的other hints。所以執行此命令之後，任何的Transact-SQL將會忽略任何的other hints.

上述的 hints 都可以進行合併，如同下列的範例：

DBCC TRACEON(8602, 8755, 8722)
這個命令是關閉目前連接中所有的 hints。

現在，我們來關閉一個或多個 hints 在整個Server上，不只是目前的連接。

DBCC TRACEON(8602, 8755, 8722, -1)
經由增加 -1，即可達成針對整個服務進行。

一但你執行了上述的指令，你需要再關閉停用的部份，如果你只是關閉目前的連線，只要取消連接即可，但是如果你是針對整個Server時，就需要再執行一次下列的指令進行啟用。

DBCC TRACEOFF(8602, 8755, 8722, -1)
使用上述的命令，整個Server任何的關閉停用的部份，將會恢復正常。

        上述的指令，將如何的運用在真實的世界中呢？方法上可以先開啟一個查詢視窗，執行程式碼，然後再關閉所有的 hints，然後再執行一次後，並觀察執行計畫的行為與執行時間，但在此時，暫不需要更改任何的程式碼。

        另外一個方法是透過Profiler追踨工作負載，分類並排序彼此執行時間，然後關閉整個伺服器中的hints，再啟用另一個Profiler追踨工作負載，同樣的也進行分類與排序。透過這個方法，你可以比較出兩個查詢之間的差異。這個方法雖然也是很複雜，但是總比單獨的進行各自的測試上，還要來的簡單。

        如果你加入一個 hints 但是卻未使用？可能是因為你加入的 hints 被SQL Server忽略了。舉例來說，如果你指定一個 index hint 到查詢中，但是查詢優化器不需去存取表格，因為他有找到一個相關的Indexed View去取代，此時hint就會被忽略。

在另外的案例上，locking hints也是有可能被忽略的，當 hints 的欄位中包含計算欄位時，這些欄位是透過 function 進行計算在其他的表格中。

在更多的案例上，如果你注意到，hints 在使用上常常會有指定，但是也是有可能被忽視的，因為可能你的誤用或錯誤所造成。如果一個 hints 沒有使用到，檢查你的 hints 去看看是有仍有需要，藉以更正確的管理 hints 的使用。

相關文章：

1. 你是否需要 SQL Server Query Hint ?

參考網址：http://www.sql-server-performance.com/2006/hints-general/
關鍵字：SQL Server、Hints、Performance Turning、效能調整