性能調優5：執行計劃

查詢優化器基於當前的統計信息和參數，衡量開銷之後，選擇“最優”的執行計劃，需要註意的是，“最優”是相對的，優化器不可能窮舉所有的執行計劃來評估其開銷，這個“最優”的標準是對當前參數和當前的統計信息來說的，優化器從生成的備選執行計劃中選擇開銷最小的。由於執行計劃的編譯和生成是很耗費資源和時間的，因此，SQL Server會把生成的任一執行計劃緩存起來，以便重用。

由於關係表的數據和結構可能發生改變，數據更新會導致統計信息過時，而之前的參數可能不具有代表性，使得已生成的執行計劃不能代表其他參數值，導致查詢性能低下。因此，應當監控執行計劃的性能，當發現參數嗅探問題時，應該及時修改代碼以重編譯；當發現統計信息過期時，應及時更新統計信息等。

一，緩存機制

SQL Server使用特定的緩存機制，以重用第一次執行查詢時生成的執行計劃，總的來說，SQL Server內部有以下四種執行計劃緩存機制：

• Ad hoc 查詢緩存
• 參數化Ad Hoc查詢緩存
• sys.sp_executesql 執行的查詢，是一種參數化的查詢語句
• 存儲過程

對於Adhoc查詢的緩存，是SQL Server自動進行的，用戶不能幹預，而後兩種是用戶可以干預的，用戶可以通過優化代碼來複用“模板化”的查詢。所謂模板化語句，是指除了個別的常量發生變化之外，語句主體不變，可以把變化的常量作為一個參數，不變的語句主體作為一個模板來處理，SQL Server優化器把這個模板編譯成執行計劃，傳入不同的參數會使用相同的執行計劃。

1，Ad Hoc查詢緩存

對於任意一個Ad Hoc查詢，SQL Server都會緩存它的執行計劃，但是，只有當批處理語句的文本完全匹配時，才會復用已緩存的執行計劃，完全匹配的處理過程是:

• SQL Server根據批處理語句的文本計算出一個Hash值，對後續的Ad Hoc查詢的文本同樣計算Hash值，當兩個Hash值相同時，說明兩個批處理的文本完全相同，相當於同一個查詢的重覆執行，SQL Server優化器會復用已緩存的執行計劃。
• 如果Ad Hoc查詢的文本有任意一個字元發生變化（比如，大寫字元變小寫字元，不同的換行，多了一個空格等），都會導致計算出的Hash值不同，進而不能復用執行計劃。也就是說，Ad Hoc查詢的文本必須完全匹配才能復用執行計劃。

大量的Ad Hoc查詢緩存會占用計劃緩存的空間，這些緩存可能只會被使用一次，以後再也不會被使用。如果資料庫系統中存在大量的一次性查詢語句，應設置Server 級別的性能優化選項：Optimize for Ad hoc Wrokloads。

“針對即席工作負載進行優化”是一個Server級別的性能優化選項，用於提高包含許多臨時批處理的工作負載的計劃緩存的效率，如果把該選項設置為True，則資料庫引擎在首次編譯批處理時只保留計劃緩存中的一個存根，而不是存儲整個執行計劃。當再次調用該批處理時，資料庫引擎識別出該批處理在之前被執行過，進而從計劃緩存中刪除該執行計劃的存根，並把完全編譯的執行計劃添加到計劃緩存中。當非參數化的Ad-Hoc查詢較多時，可以避免計劃緩存存儲過多的不會被覆用的執行計劃。

2，參數化Ad-Hoc

SQL Server 自主決定是否把查詢中的常量作為參數來對待，除了常量不同之外，其他語句主體都相同，這就是這個查詢語句的模板，不同的參數使用相同的執行計劃。

例如，對於以下兩個查詢語句，除了常量1和2不同之外，其他語句都相同，

select ID, Name from dbo.Users where ID=1
select ID, Name from dbo.Users where ID=2

SQL Server對該語句做參數化處理，得到模板，只要語句符合該模板，優化器就復用已緩存的執行計劃。

select ID, Name from dbo.Users where ID=@id

3，Prepared 查詢緩存

用戶使用sys.sp_executesql 控制參數和模板，只要模板相同，而參數不同，都可以復用已緩存的執行計劃。

4，存儲過程

用戶創建的存儲過程，在第一次執行時，編譯和生成執行計劃，並緩存到計劃緩存中，當下次調用相同的存儲過程，即使使傳遞的參數不同，SQL Server都會復用執行計劃。

二，參數嗅探

參數嗅探是指在創建存儲過程，或者參數化查詢的執行計劃時，根據傳入的參數進行預估並生成執行計劃。SQL Server生成的執行計劃對當前參數來說是最優的，而對其他大多數參數來說，是非常低效的。有些時候，針對一個查詢的第一次傳參，已經產生了一個執行計劃，當後續傳參時，由於存在對應參數的數據分佈等問題，導致原有的執行計劃無法高效地響應查詢請求，這就出現參數嗅探問題。

參數嗅探的本質是優化器根據參數來生成的執行計劃不是最優的，導致優化器在復用執行計劃時，語句的查詢性能變得十分低下。對於參數嗅探問題，必須重新生成執行計劃，可以使用語句重編譯，編譯提示（optimize for）等功能來避免。

三，影響執行計劃復用的因素

SQL Server不會永久保存計劃的緩存，並且存在緩存中的執行計劃也不會永久不變，每個計劃都會有一個Age值，當SQL Server探測到記憶體壓力時，會觸發Lazy Writer進程，用於清空所有的臟頁，釋放數據緩存。當掃面到計劃緩存時，會降低Age值，當復用一次計劃時，會增加Age值。當系統遇到記憶體壓力，或Age值降到0時，執行計劃會被移除記憶體。

除了這兩個條件之外，當遇到下麵的條件時，執行計劃一會被移除記憶體，被重新編譯：

• 查詢引用的基礎表的結構被更改
• 查詢引用的索引被更改或被刪除
• 查詢引用的統計信息被更新
• 執行計劃被強制重新編譯（詳見本問第四小節）
• 單一查詢中混合了DDL和DML操作，也稱為延遲編譯
• 在查詢中修改set選項
• 查詢所用到的臨時表的結構被修改
• 等等

在執行計劃執行過程中，執行計劃被重新編譯，是優化器根據表結構，索引結構和統計信息做出優化的結構，目的是為了避免繼續使用不合適的執行計劃。

四，強制重新編譯執行計劃

修改存儲過程，觸發器等模塊（Module）能夠使其執行計劃重新編譯，除此之外，還有其他方法，能夠強制重新編譯執行計劃

1，標記，下次重新編譯

使用該存儲過程，標記一個執行模塊（SP，Trigger，User-Defined Function）在下次執行時，重新編譯執行計劃

sys.sp_recompile [ @objname = ] 'object'

2，不復用執行計劃

在創建存儲過程時，使用WITH RECOMPILE 選項，在每次執行SP時，都重新編譯，使用新的執行計劃。

CREATE PROCEDURE dbo.usp_procname 
    @Parameter_Name varchar(30) = 'Parameter_default_value'
WITH RECOMPILE

3，執行時重新編譯

在執行存儲過程時，重新編譯存儲過程的執行計劃

exec dbo.usp_procname @Parameter_name='Parameter_value' 
WITH RECOMPILE

4，語句級別的重新編譯

在SP中，使用查詢選項 option（recompile），只重新編譯該語句級別的執行計劃

select column_name_list
from dbo.tablename
option(recompile)

SQL Server在執行查詢之後，查詢提示（RECOMPILE）指示存儲引擎將計劃緩存拋棄，在下次執行存儲過程時，強制查詢優化器重新編譯，生成新的執行計劃。在重新編譯時，SQL Server 優化器使用當前的變數值生成新的計劃緩存。

五，控制執行計劃

優化器會根據查詢選擇執行計劃，選擇索引，表關聯演算法等，但是，當發現優化器選擇了低效的執行計劃時，可以使用hint來控制執行計劃，SQL Server提供了三種類型的hint：

• 查詢提示（query hint）：告知優化器在整個查詢過程中都應用某個提示，
• 關聯提示（join hint）：告知優化器在關聯時使用特定的關聯演算法
• 表提示（table hint）：告知優化器使用表掃描，還是表上特定的索引

1，查詢提示

使用option來設置查詢提示，

• 用於group by 聚合，可以控制分組的演算法：hash group 和order group
• 用於控制關聯的演算法， option(hash join)
• 通常情況下，優化器決定表關聯的順序，可以使用force order選項，使優化器按照join的順序來關聯， option(force order)
• 使用maxdop來確定語句執行的最大併發度，option(maxdop 1)，取消併發執行。
• 按照指定的參數來優化 option(optimize for (@para_name= constant_value))

2，關聯提示

在 join關鍵字前面使用Loop，Merge和Hash來控制關聯的演算法

3，表提示

在引用的表名後面，通過with()來設置表提示 table_name with(hints)，

當使用索引時，使用 with(index(index_name))來設置，

參考文檔：