簡介

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

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

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

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

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

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

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

SELECT * 
FROM A INNER JOIN B ON a.a=b.b
INNER JOIN C ON c.c=a.a

代碼清單1.

對于該查詢來說，無論A先Inner join B還是B先Inner Join C，結(jié)果都是一樣的，因此可以生成多個(gè)執(zhí)行計(jì)劃，但一個(gè)基本原則是SQL Server不一定會選擇最好的執(zhí)行計(jì)劃，而是選擇足夠好的計(jì)劃，這是由于評估所有的執(zhí)行計(jì)劃的成本所消耗的成本不應(yīng)該過大。最終，SQL Server會根據(jù)數(shù)據(jù)的基數(shù)和每一步所消耗的CPU和IO的成本來評估執(zhí)行計(jì)劃的成本，所以執(zhí)行計(jì)劃的選擇重度依賴于統(tǒng)計(jì)信息，關(guān)于統(tǒng)計(jì)信息的相關(guān)內(nèi)容，我就不細(xì)說了。

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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.通過執(zhí)行計(jì)劃緩存找到數(shù)據(jù)庫總耗時(shí)最長的20個(gè)查詢語句

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

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

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

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

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

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

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

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

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

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

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

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

在下篇文章中，我們將會繼續(xù)來看由于執(zhí)行計(jì)劃緩存和查詢分析器的沖突，以及編譯執(zhí)行計(jì)劃所帶來的常見問題和解決方案。

小結(jié)

本篇文章中，我們簡單講述了查詢優(yōu)化器生成執(zhí)行計(jì)劃的過程，以及執(zhí)行計(jì)劃緩存的機(jī)制。當(dāng)查詢優(yōu)化器和執(zhí)行計(jì)劃緩存以某種不好的情況交匯時(shí)，將產(chǎn)生一些問題。在下篇文章中，我們會繼續(xù)探索SQL Server中的執(zhí)行計(jì)劃緩存。

以上內(nèi)容是小編給大家介紹的SQL Server中的執(zhí)行計(jì)劃緩存(上)的全部敘述，希望大家喜歡。