怎樣淺析SQL Server中執(zhí)行計劃的相關(guān)機制以及常見問題

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

簡介

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

為什么需要執(zhí)行計劃緩存

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

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

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

第三步根據(jù)數(shù)據(jù)庫中的統(tǒng)計信息，進行基于成本（Cost-based）的評估。

上面三個步驟完成之后，才會生成多個候選執(zhí)行計劃。雖然我們的SQL語句邏輯上只有一個，但是符合這個邏輯順序的物理獲取數(shù)據(jù)的順序卻可以有多條，打個比方，你希望從北京到上海，即可以做高鐵，也可以做飛機，但從北京到上海這個描述是邏輯描述，具體怎么實現(xiàn)路徑有多條。那讓我們再看一個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，結(jié)果都是一樣的，因此可以生成多個執(zhí)行計劃，但一個基本原則是SQL Server不一定會選擇最好的執(zhí)行計劃，而是選擇足夠好的計劃，這是由于評估所有的執(zhí)行計劃的成本所消耗的成本不應(yīng)該過大。最終，SQL Server會根據(jù)數(shù)據(jù)的基數(shù)和每一步所消耗的CPU和IO的成本來評估執(zhí)行計劃的成本，所以執(zhí)行計劃的選擇重度依賴于統(tǒng)計信息，關(guān)于統(tǒng)計信息的相關(guān)內(nèi)容，我就不細說了。

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

執(zhí)行計劃所緩存的對象

執(zhí)行計劃所緩存的對象分為4類，分別是：

編譯后的計劃：編譯的執(zhí)行計劃和執(zhí)行計劃的關(guān)系就和MSIL和C#的關(guān)系一樣。

執(zhí)行上下文：在執(zhí)行編譯的計劃時，會有上下文環(huán)境。因為編譯的計劃可以被多個用戶共享，但查詢需要存儲SET信息以及本地變量的值等，因此上下文環(huán)境需要對應(yīng)執(zhí)行計劃進行關(guān)聯(lián)。執(zhí)行上下文也被稱為Executable Plan。

游標：存儲的游標狀態(tài)類似于執(zhí)行上下文和編譯的計劃的關(guān)系。游標本身只能被某個連接使用，但游標關(guān)聯(lián)的執(zhí)行計劃可以被多個用戶共享。

代數(shù)樹：代數(shù)樹（也被稱為解析樹）代表著查詢文本。正如我們之前所說，查詢分析器不會直接引用查詢文本，而是代數(shù)樹。這里或許你會有疑問，代數(shù)樹用于生成執(zhí)行計劃，這里還緩存代數(shù)樹干毛??？這是因為視圖、Default、約束可能會被不同查詢重復(fù)使用，將這些對象的代數(shù)樹緩存起來省去了解析的過程。

比如說我們可以通過dm_exec_cached_plans這個DMV找到被緩存的執(zhí)行計劃，如圖1所示。

圖1.被緩存的執(zhí)行計劃

那究竟這幾類對象緩存所占用的內(nèi)存相關(guān)信息該怎么看呢？我們可以通過dm_os_memory_cache_counters這個DMV看到，上述幾類被緩存的對象如圖2所示。

圖2.在內(nèi)存中這幾類對象緩存所占用的內(nèi)存

另外，執(zhí)行計劃緩存是一種緩存。而緩存中的對象會根據(jù)算法被替換掉。對于執(zhí)行計劃緩存來說，被替換的算法主要是基于內(nèi)存壓力。而內(nèi)存壓力會被分為兩種，既內(nèi)部壓力和外部壓力。外部壓力是由于Buffer Pool的可用空間降到某一臨界值（該臨界值會根據(jù)物理內(nèi)存的大小而不同，如果設(shè)置了最大內(nèi)存則根據(jù)最大內(nèi)存來）。內(nèi)部壓力是由于執(zhí)行計劃緩存中的對象超過某一個閾值，比如說32位的SQL Server該閾值為40000，而64位中該值被提升到了160000。

這里重點說一下，緩存的標識符是查詢語句本身，因此select * from SchemaName.TableName和Select * from TableName雖然效果一致，但需要緩存兩份執(zhí)行計劃，所以一個Best Practice是在引用表名稱和以及其他對象的名稱時，請帶上架構(gòu)名稱。基于被緩存的執(zhí)行計劃對語句進行調(diào)優(yōu)

被緩存的執(zhí)行計劃所存儲的內(nèi)容非常豐富，不僅僅包括被緩存的執(zhí)行計劃、語句，還包括被緩存執(zhí)行計劃的統(tǒng)計信息，比如說CPU的使用、等待時間等。但這里值得注意的是，這里的統(tǒng)計只算執(zhí)行時間，而不算編譯時間。比如說我們可以利用代碼清單2中的代碼根據(jù)被緩存的執(zhí)行計劃找到數(shù)據(jù)庫中耗時最長的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.通過執(zhí)行計劃緩存找到數(shù)據(jù)庫總耗時最長的20個查詢語句

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

相比較于無論是服務(wù)端Trace還是客戶端的Profiler，該方法有一定優(yōu)勢，如果通過捕捉Trace再分析的話，不僅費時費力，還會給服務(wù)器帶來額外的開銷，通過該方法找到耗時的查詢語句就會簡單很多。但是該統(tǒng)計僅僅基于上次實例重啟或者沒有運行DBCC FreeProcCache之后。但該方法也有一些弊端，比如說：

類似索引重建、更新統(tǒng)計信息這類語句是不緩存的，而這些語句成本會非常高。緩存可能隨時會被替換掉，因此該方法無法看到不再緩存中的語句。該統(tǒng)計信息只能看到執(zhí)行成本，無法看到編譯成本。沒有參數(shù)化的緩存可能同一個語句呈現(xiàn)不同的執(zhí)行計劃，因此出現(xiàn)不同的緩存，在這種情況下統(tǒng)計信息無法累計，可能造成不是很準確。

執(zhí)行計劃緩存和查詢優(yōu)化器的矛盾

還記得我們之前所說的嗎，執(zhí)行計劃的編譯和選擇分為三步，其中前兩步僅僅根據(jù)查詢語句和表等對象的metadata，在執(zhí)行計劃選擇的階段要重度依賴于統(tǒng)計信息，因此同一個語句僅僅是參數(shù)的不同，查詢優(yōu)化器就會產(chǎn)生不同的執(zhí)行計劃，比如說我們來看一個簡單的例子，如圖3所示。

圖3.僅僅是由于不同的參數(shù)，查詢優(yōu)化器選擇不同的執(zhí)行計劃

大家可能會覺得，這不是挺好的嘛，根據(jù)參數(shù)產(chǎn)生不同的執(zhí)行計劃。那讓我們再考慮一個問題，如果將上面的查詢放到一個存儲過程中，參數(shù)不能被直接嗅探到，當?shù)谝粋€執(zhí)行計劃被緩存后，第二次執(zhí)行會復(fù)用第一次的執(zhí)行計劃！雖然免去了編譯時間，但不好的執(zhí)行計劃所消耗的成本會更高！讓我們來看這個例子，如圖4所示。

圖4.不同的參數(shù)，卻是完全一樣的執(zhí)行計劃！

再讓我們看同一個例子，把執(zhí)行順序顛倒后，如圖5所示。

圖5.執(zhí)行計劃完全變了

我們看到，第二次執(zhí)行的語句，完全復(fù)用了第一次的執(zhí)行計劃。那總會有一個查詢犧牲。比如說當參數(shù)為4時會有5000多條，此時索引掃描應(yīng)該最高效，但圖4卻復(fù)用了上一個執(zhí)行計劃，使用了5000多次查找?。?！這無疑是低效率的。而且這種情況出現(xiàn)會非常讓DBA迷茫，因為在緩存中的執(zhí)行計劃不可控，緩存中的對象隨時可能被刪除，誰先執(zhí)行誰后執(zhí)行產(chǎn)生的性能問題往往也讓DBA頭疼。

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

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