怎樣淺析SQL Server中執行計劃的相關機制以及常見問題

怎樣淺析SQL Server中執行計劃的相關機制以及常見問題，相信很多沒有經驗的人對此束手無策，為此本文總結了問題出現的原因和解決方法，通過這篇文章希望你能解決這個問題。

簡介

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

為什么需要執行計劃緩存

從簡介中我們知道，生成執行計劃的過程步驟所占的比例眾多，會消耗掉各CPU和內存資源。而實際上，查詢優化器生成執行計劃要做更多的工作，大概分為3部分：

首先，根據傳入的查詢語句文本，解析表名稱、存儲過程名稱、視圖名稱等。然后基于邏輯數據操作生成代表查詢文本的樹。

第二步是優化和簡化，比如說將子查詢轉換成對等的連接、優先應用過濾條件、刪除不必要的連接（比如說有索引，可能不需要引用原表）等。

第三步根據數據庫中的統計信息，進行基于成本（Cost-based）的評估。

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

SELECT * FROM A INNER JOIN B ON a.a=b.bINNER 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 UNCOMMITTEDSELECT 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頭疼。

由這個例子我們看出，查詢優化器希望盡可能選擇高效的執行計劃，而執行計劃緩存卻希望盡可能的重用緩存，這兩種機制在某些情況會產生沖突。

看完上述內容，你們掌握怎樣淺析SQL Server中執行計劃的相關機制以及常見問題的方法了嗎？如果還想學到更多技能或想了解更多相關內容，歡迎關注億速云行業資訊頻道，感謝各位的閱讀！