談一談SQL Server中的執行計畫緩衝

[轉]http://www.cnblogs.com/CareySon/archive/2013/05/04/PlanCacheInSQLServer.html

簡介

    我們平時所寫的SQL語句本質只是擷取資料的邏輯，而不是擷取資料的實體路徑。當我們寫的SQL語句傳到SQL Server的時候，查詢分析器會將語句依次進行解析（Parse）、綁定（Bind）、查詢最佳化（Optimization，有時候也被稱為簡化）、執行（Execution）。除去執行步驟外，前三個步驟之後就產生了執行計畫，也就是SQL Server按照該計劃擷取物理資料方式，最後執行步驟按照執行計畫執行查詢從而獲得結果。但查詢最佳化工具不是本篇的重點，本篇文章主要講述查詢最佳化工具在產生執行計畫之後，緩衝執行計畫的相關機制以及常見問題。

 

為什麼需要執行計畫緩衝

    從簡介中我們知道，產生執行計畫的過程步驟所佔的比例眾多，會消耗掉各CPU和記憶體資源。而實際上，查詢最佳化工具產生執行計畫要做更多的工作，大概分為3部分：

• 首先，根據傳入的查詢語句文本，解析表名稱、預存程序名稱、視圖名稱等。然後基於邏輯資料操作產生代表查詢文本的樹。
• 第二步是最佳化和簡化，比如說將子查詢轉換成對等的串連、優先應用過濾條件、刪除不必要的串連（比如說有索引，可能不需要引用原表）等。
• 第三步根據資料庫中的統計資訊，進行基於成本（Cost-based）的評估。

 

    上面三個步驟完成之後，才會產生多個候選執行計畫。雖然我們的SQL語句邏輯上只有一個，但是符合這個邏輯順序的物理擷取資料的順序卻可以有多條，打個比方，你希望從北京到上海，即可以做高鐵，也可以做飛機，但從北京到上海這個描述是邏輯描述，具體怎麼實現路徑有多條。那讓我們再看一個SQL Server中的舉例，比如代碼清單1中的查詢。

   1: SELECT * 

   2: FROM A INNER JOIN B ON a.a=b.b

   3: INNER JOIN C ON c.c=a.a

代碼清單1.

 

    對於該查詢來說，無論A先Inner join B還是B先Inner Join C，結果都是一樣的，因此可以產生多個執行計畫，但一個基本原則是SQL Server不一定會選擇最好的執行計畫，而是選擇足夠好的計劃，這是由於評估所有的執行計畫的成本所消耗的成本不應該過大。最終，SQL Server會根據資料的基數和每一步所消耗的CPU和IO的成本來評估執行計畫的成本，所以執行計畫的選擇重度依賴於統計資訊，關於統計資訊的相關內容，我就不細說了。

    對於前面查詢分析器產生執行計畫的過程不難看出，該步驟消耗的資源成本也是驚人的。因此當同樣的查詢執行一次以後，將其緩衝起來將會大大減少執行計畫的編譯，從而提高效率，這就是執行計畫緩衝存在的初衷。

 

執行計畫所緩衝的對象

    執行計畫所緩衝的對象分為4類，分別是：

• 編譯後的計劃：編譯的執行計畫和執行計畫的關係就和MSIL和C#的關係一樣。
• 執行內容：在執行編譯的計劃時，會有上下文環境。因為編譯的計劃可以被多個使用者共用，但查詢需要儲存SET資訊以及本地變數的值等，因此上下文環境需要對應執行計畫進行關聯。執行內容也被稱為Executable Plan。
• 遊標：儲存的遊標狀態類似於執行內容和編譯的計劃的關係。遊標本身只能被某個串連使用，但遊標關聯的執行計畫可以被多個使用者共用。
• 代數樹：代數樹（也被稱為解析樹）代表著查詢文本。正如我們之前所說，查詢分析器不會直接引用查詢文本，而是代數樹。這裡或許你會有疑問，代數樹用於產生執行計畫，這裡還緩衝代數樹榦毛啊？這是因為視圖、Default、約束可能會被不同查詢重複使用，將這些對象的代數樹緩衝起來省去瞭解析的過程。

 

    比如說我們可以通過dm_exec_cached_plans這個DMV找到被緩衝的執行計畫，1所示。

圖1.被緩衝的執行計畫

 

    那究竟這幾類對象緩衝所佔用的記憶體相關資訊該怎麼看呢？我們可以通過dm_os_memory_cache_counters這個DMV看到，上述幾類被緩衝的對象2所示。

圖2.在記憶體中這幾類對象緩衝所佔用的記憶體

 

    另外，執行計畫緩衝是一種緩衝。而緩衝中的對象會根據演算法被替換掉。對於執行計畫緩衝來說，被替換的演算法主要是基於記憶體壓力。而記憶體壓力會被分為兩種，既內部壓力和外部壓力。外部壓力是由於Buffer Pool的可用空間降到某一臨界值（該臨界值會根據實體記憶體的大小而不同，如果設定了最大記憶體則根據最大記憶體來）。內部壓力是由於執行計畫緩衝中的對象超過某一個閾值，比如說32位的SQL Server該閾值為40000，而64位中該值被提升到了160000。

這裡重點說一下，緩衝的標識符是查詢語句本身，因此select * from SchemaName.TableName和Select * from TableName雖然效果一致，但需要緩衝兩份執行計畫，所以一個Best Practice是在參考資料表名稱和以及其他對象的名稱時，請帶上架構名稱。

   

基於被緩衝的執行計畫對語句進行調優

    被緩衝的執行計畫所儲存的內容非常豐富，不僅僅包括被緩衝的執行計畫、語句，還包括被緩衝執行計畫的統計資訊，比如說CPU的使用、等待時間等。但這裡值得注意的是，這裡的統計只算執行時間，而不算編譯時間。比如說我們可以利用代碼清單2中的代碼根據被緩衝的執行計畫找到資料庫中耗時最長的20個查詢語句。

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 

SELECT TOP 20 

  CAST(qs.total_elapsed_time / 1000000.0 AS DECIMAL(28, 2)) 

                                     AS [Total Duration (s)] 

  , CAST(qs.total_worker_time * 100.0 / qs.total_elapsed_time 

                               AS DECIMAL(28, 2)) AS [% CPU] 

  , CAST((qs.total_elapsed_time - qs.total_worker_time)* 100.0 / 

        qs.total_elapsed_time AS DECIMAL(28, 2)) AS [% Waiting] 

  , qs.execution_count 

  , CAST(qs.total_elapsed_time / 1000000.0 / qs.execution_count 

                AS DECIMAL(28, 2)) AS [Average Duration (s)] 

  , SUBSTRING (qt.text,(qs.statement_start_offset/2) + 1,      

    ((CASE WHEN qs.statement_end_offset = -1 

      THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 

      ELSE qs.statement_end_offset 

      END - qs.statement_start_offset)/2) + 1) AS [Individual Query 

  , qt.text AS [Parent Query] 

  , DB_NAME(qt.dbid) AS DatabaseName 

  , qp.query_plan 

FROM sys.dm_exec_query_stats qs 

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt 

CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp 

WHERE qs.total_elapsed_time > 0 

ORDER BY qs.total_elapsed_time DESC     

代碼清單2.通過執行計畫緩衝找到資料庫總耗時最長的20個查詢語句

 

    上面的語句您可以修改Order By來根據不同的條件找到你希望找到的語句，這裡就不再細說了。

    相比較於無論是服務端Trace還是用戶端的Profiler，該方法有一定優勢，如果通過捕捉Trace再分析的話，不僅費時費力，還會給伺服器帶來額外的開銷，通過該方法找到耗時的查詢語句就會簡單很多。但是該統計僅僅基於上次執行個體重啟或者沒有運行DBCC FreeProcCache之後。但該方法也有一些弊端，比如說：

• 類似索引重建、更新統計資料這類語句是不緩衝的，而這些語句成本會非常高。
• 緩衝可能隨時會被替換掉，因此該方法無法看到不再緩衝中的語句。
• 該統計資訊只能看到執行成本，無法看到編譯成本。
• 沒有參數化的緩衝可能同一個語句呈現不同的執行計畫，因此出現不同的緩衝，在這種情況下統計資訊無法累計，可能造成不是很準確。

 

執行計畫緩衝和查詢最佳化工具的矛盾

    還記得我們之前所說的嗎，執行計畫的編譯和選擇分為三步，其中前兩步僅僅根據查詢語句和表等對象的metadata，在執行計畫選擇的階段要重度依賴於統計資訊，因此同一個語句僅僅是參數的不同，查詢最佳化工具就會產生不同的執行計畫，比如說我們來看一個簡單的例子，3所示。

圖3.僅僅是由於不同的參數，查詢最佳化工具選擇不同的執行計畫

 

    大家可能會覺得，這不是挺好的嘛，根據參數產生不同的執行計畫。那讓我們再考慮一個問題，如果將上面的查詢放到一個預存程序中，參數不能被直接嗅探到，當第一個執行計畫被緩衝後，第二次執行會複用第一次的執行計畫！雖然免去了編譯時間，但不好的執行計畫所消耗的成本會更高！讓我們來看這個例子，4所示。

圖4.不同的參數，卻是完全一樣的執行計畫！

 

    再讓我們看同一個例子，把執行順序顛倒後，5所示。

圖5.執行計畫完全變了

 

    我們看到，第二次執行的語句，完全複用了第一次的執行計畫。那總會有一個查詢犧牲。比如說當參數為4時會有5000多條，此時索引掃描應該最高效，但圖4卻複用了上一個執行計畫，使用了5000多次尋找！！！這無疑是低效率的。而且這種情況出現會非常讓DBA迷茫，因為在緩衝中的執行計畫不可控，緩衝中的對象隨時可能被刪除，誰先執行誰後執行產生的效能問題往往也讓DBA頭疼。

   由這個例子我們看出，查詢最佳化工具希望儘可能選擇高效的執行計畫，而執行計畫緩衝卻希望儘可能的重用緩衝，這兩種機制在某些情況會產生衝突。

    在下篇文章中，我們將會繼續來看由於執行計畫緩衝和查詢分析器的衝突，以及編譯執行計畫所帶來的常見問題和解決方案。

 

將執行緩衝考慮在內時的流程

    上篇文章中提到了查詢最佳化工具解析語句的過程，當將計畫快取考慮在內時，首先需要查看計畫快取中是否已經有語句的緩衝，如果沒有，才會執行編譯過程，如果存在則直接利用編譯好的執行計畫。因此，完整的過程1所示。

圖1.將計畫快取考慮在內的過程

 

    圖1中我們可以看到，其中有一步需要在緩衝中找到計劃的過程。因此不難猜出，只要是這一類尋找，一定跑不了散列（Hash）的資料結構。通過sys.dm_os_memory_cache_hash_tables這個DMV可以找到有關該Hash表的一些資訊，2所示。這裡值得注意的是，當執行計畫過多導致散列後的對象在同一個Bucket過多時，則需要額外的Bucket，因此可能會導致尋找計畫快取效率低下。解決辦法是盡量減少在計畫快取中的計劃個數，我們會在本文後面討論到。

圖2.有關儲存計畫快取的HashTable的相關資訊

 

    當出現這類問題時，我們可以在buckets_avg_scan_miss_length列看出問題。這類情況在快取命中率（SQL Server: Plan Cache-Cache Hit Ratio）比較高，但編譯時間過長時可以作為考慮對象。

 

參數化和非參數化

    查詢計劃的唯一標識是查詢語句本身，但假設語句的主體一樣，而僅僅是查詢條件謂詞不一樣，那在執行計畫中算1個執行計畫還是兩個執行計畫呢？It’s Depends。

    假設下面兩個語句,3所示。

圖3.僅僅謂詞條件不一樣的兩個語句

 

    雖然執行計畫一樣，但是在執行計畫緩衝中卻會保留兩份執行計畫，4所示。

圖4.同一個語句，不同條件，有兩份不同的執行計畫緩衝

 

    我們知道，執行計畫緩衝依靠查詢語句本身來判別緩衝，因此上面兩個語句在執行計畫緩衝中就被視為兩個不同的語句。那麼解決該問題的手段就是使得執行計畫緩衝中的查詢語句一模一樣。

 

參數化

    使得僅僅是某些參數不同，而查詢本身相同的語句可以複用，就是參數化的意義所在。比如說圖3中的語句，如果我們啟用了資料庫的強制參數化，或是使用預存程序等。SQL Server會將這些語句強制參數話，比如說我們根據圖5修改了資料庫層級的選項。

圖5.資料庫層級的選項

 

   此時我們再來執行圖3中的兩條語句，通過查詢執行計畫緩衝，我們發現變數部分被參數化了，從而在計畫快取中的語句變得一致，6所示，從而可以複用.

圖6.參數話之後的查詢語句

 

   但是，強制參數會引起一些問題，查詢最佳化工具很多時候就無法根據統計資訊最佳化一些具體的查詢，比如說不能應用一些索引或者該掃描的時候卻尋找。所產生的負面影響在上篇文章中已經說過，這裡就不細說了。

   因此對於上面的問題可以有幾種解決辦法。

 

平衡參數化和非參數化

    在具體的情況下，參數化有些時候是好的,但有些時候卻是效能問題的罪魁禍首，下面我們來看幾種平衡這兩者之間關係的手段。

 

使用RECOMPILE

    當查詢中，不準確的執行計畫的成本要高於編譯的成本時，在預存程序中使用RECOMPILE選項或是在即席查詢中使用RECOMPILE提示使得每次查詢都會重建執行計畫，該參數會使得產生的執行計畫不會被插入到執行計畫緩衝中。對於OLAP類查詢來說，不準確的執行計畫所耗費的成本往往高於編譯成本太多，所以可以考慮該參數或選項，您可以如代碼清單1中的查詢所示這樣使用Hint。

SELECT * FROM Sales.Customer

WHERE CustomerID>20000 AND TerritoryID = 4

OPTION (recompile)

代碼清單1.使用Recompile

 

   除去我們可以手動提示SQL Server重編譯之外，SQL Server也會在下列條件下自動重編譯：

• 中繼資料變更，比如說表明稱改變、刪除列、變更資料類型等。
• 統計資訊變更。
• 串連的SET參數變化，SET ANSI_NULLS等的值不一樣，會導致緩衝的執行計畫不能被複用，從而重編譯。這也是為什麼我們看到緩衝的執行計畫中語句一模一樣，但就是不複用，還需要相關的參數一致，這些參數可以通過sys.dm_exec_plan_attributes來查看。

 

使用Optimize For參數

    RECOMPILE方式提供了完全不使用計畫快取的節奏。但有些時候，特性謂語的執行計畫被使用的次數h更多，比如說，僅僅那些謂語條件產生大量返回結果集的參數編譯，我們可以考慮Optimize For參數。比如我們來看代碼清單2。

DECLARE @vari INT

SET @vari=4

SELECT * FROM Sales.Customer

WHERE CustomerID>20000 AND TerritoryID = @vari

OPTION (OPTIMIZE FOR (@vari=4))

代碼清單2.使用OPTIMIZE FOR提示

 

    使用了該參數會使得緩衝的執行計畫按照OPTIMIZE FOR後面的謂語條件來產生並緩衝執行計畫，這也可能造成不在該參數中的查詢效率低下，但是該參數是我們選擇的，因此通常我們知道哪些謂語條件會被使用的多一些。

 

   另外，自SQL Server 2008開始多了一個OPTIMIZE FOR UNKNOWN參數，這使得在最佳化查詢的過程中探測作為謂語條件的局部參數的值，而不是根據局部變數的初始值去探測統計資訊。

 

在預存程序中使用局部變數代替預存程序參數

    在預存程序中不使用過程參數，而是使用局部變數相當於直接禁用參數嗅探。畢竟，局部變數的值只有在運行時才能知道，在執行計畫被查詢最佳化工具編譯時間是無法知道該值的，因此強迫查詢分析器使用條件列的平均值進行估計。

    雖然這種方式使得參數估計變得非常不準確，但是會變得非常穩定，畢竟統計資訊不會變更的過於頻繁。該方式不被推薦,如果可能，盡量使用Optimizer的方式。

    代碼清單3展示了這種方式。

CREATE PROC TestForLocalVari

@v INT

AS

DECLARE @vari INT

SET @vari=@v

SELECT * FROM Sales.Customer

WHERE CustomerID>20000 AND TerritoryID = @vari

代碼清單3.直接引用局部變數，而不是預存程序參數

 

強制參數化

    在本篇文章的前面已經提到過了強制參數化，這裡就不再提了。

 

使用計劃指導

    在某些情況下，我們的環境不允許我們直接修改SQL語句，比如所不希望破壞代碼的邏輯性或是應用程式是第三方開發，因此無論是加HINT或參數都變得不現實。此時我們可以使用計劃指導。

    計劃指導使得查詢語句在由用戶端應用程式扔到SQL Server的時候，SQL Server對其加上提示或選項，比如說通過代碼清單4可以看到一個計劃指導的例子。

EXEC sp_create_plan_guide N'MyPlanGuide1',  

@stmt=N'SELECT * FROM Sales.Customer WHERE CustomerID>20000 AND TerritoryID=@vari', 

@type=N'sql',  

@module_or_batch=NULL, 

@params=N'@vari int',

@hints=N'OPTION (RECOMPILE)'

代碼清單4.對我們前面的查詢設定計劃指導

 

    當加入了計劃指導後，當批處理到達SQL Server時，在尋找匹配的計畫快取時也會去找是否有計劃指導和其相匹配。如果匹配，則應用計劃指導中的提示或選項。這裡要注意的是，這裡@stmt參數必須和查詢語句中的一句一模一樣，差一個空格都會被認為不匹配。

 

PARAMETERIZATION SIMPLE

    當我們在資料庫層級啟用了強制參數化時，對於特定語句，我們卻不想啟用強制參數化，我們可以使用PARAMETERIZATION SIMPLE選項，如代碼清單5所示。

DECLARE @stmt NVARCHAR(MAX)

DECLARE @params NVARCHAR(MAX)

EXEC sp_get_query_template N'SELECT * FROM Sales.Customer WHERE CustomerID>20000 AND TerritoryID=2',

@stmt OUTPUT, @params OUTPUT

PRINT @stmt

PRINT @params

EXEC sp_create_plan_guide N'MyTemplatePlanGuide', @stmt, N'TEMPLATE', NULL,

    @params, N'OPTION(PARAMETERIZATION SIMPLE)'

代碼清單5.通過計劃指南對單條語句應用簡單參數化

 

小結

    執行計畫緩衝希望盡量重用執行計畫，這會減少編譯所消耗的CPU和執行緩衝所消耗的記憶體。而查詢最佳化工具希望盡量產生更精準的執行計畫，這勢必會造成大量的執行計畫，這不僅僅可能引起重編譯大量消耗CPU，還會造成記憶體壓力，甚至當執行計畫緩衝過多超過BUCKET的限制時，在緩衝中匹配執行計畫的步驟也會消耗更多的時間。

    因此利用本篇文章中所述的方法基於實際的情況平衡兩者之間的關係，就變得非常重要。