[SQL SERVER][Performance]善用 OPTIMIZE FOR 查詢提示
OPTIMIZE FOR 可以讓SQL Server 在查詢最佳化期間知道使用特定數值，
但該值在查詢執行期間不使用，
這樣做的好處是可以避開查詢最佳化工具的預設參數探測行為，
下面我舉個例子相性大家會比較了解 OPTIMIZE FOR 的好處。
Query1
select top(10000) *
from ap_log
where LogDate>='20110701'
使用索引搜尋(index seek)。
Query2:利用變數取代(現實世界中的參數通常是不固定的)
declare @mydate char(8)
set @mydate='20110701'
select top(10000) *
from ap_log
where LogDate>=@mydate
使用叢集索引掃描(index scan)。
上面看上去Query1和Query2應該是一樣的，但為什麼所產生的執行計畫卻大大不同，
而且Query2效能更是差到不行，
這是因為SQL Server在查詢最佳化期間不知道該變數真正的值(類似OPTIMIZE FOR UNKNOWN)，
導致需要執行期間才能確認該變數值，
所以SQL Server需要完整執行掃瞄10000筆後才能知道該值資料是否存在，
解決這樣的問題有很多方法，例如強制執行計畫走某索引(使用 hint)、使用 recompile 選項..等，
但我覺得使用OPTIMIZE FOR 選項會是比較好的選擇，
這是因為查詢陳述式寫死 Index Name會降低可靠性，
如果那天該索引被刪除或改名稱都將造成影響，
而使用 recompile 將失去快取執行計畫的優勢。
修正Query2執行計畫
declare @mydate char(8)
select top(10000) *
from ap_log
where LogDate>=@mydate
option(OPTIMIZE FOR (@mydate = '20110701'))
參考