淺析SQL Server中的執(zhí)行計(jì)劃緩存（下）

在上篇文章給大家介紹了SQL Server中的執(zhí)行計(jì)劃緩存（上），本文繼續(xù)給大家介紹sqlserver執(zhí)行計(jì)劃緩存相關(guān)知識(shí)，小伙伴們一起學(xué)習(xí)吧。

簡(jiǎn)介

在上篇文章中我們談到了查詢(xún)優(yōu)化器和執(zhí)行計(jì)劃緩存的關(guān)系，以及其二者之間的沖突。本篇文章中，我們會(huì)主要闡述執(zhí)行計(jì)劃緩存常見(jiàn)的問(wèn)題以及一些解決辦法。

將執(zhí)行緩存考慮在內(nèi)時(shí)的流程

上篇文章中提到了查詢(xún)優(yōu)化器解析語(yǔ)句的過(guò)程，當(dāng)將計(jì)劃緩存考慮在內(nèi)時(shí)，首先需要查看計(jì)劃緩存中是否已經(jīng)有語(yǔ)句的緩存，如果沒(méi)有，才會(huì)執(zhí)行編譯過(guò)程，如果存在則直接利用編譯好的執(zhí)行計(jì)劃。因此，完整的過(guò)程如圖1所示。

圖1.將計(jì)劃緩存考慮在內(nèi)的過(guò)程

圖1中我們可以看到，其中有一步需要在緩存中找到計(jì)劃的過(guò)程。因此不難猜出，只要是這一類(lèi)查找，一定跑不了散列（Hash）的數(shù)據(jù)結(jié)構(gòu)。通過(guò)sys.dm_os_memory_cache_hash_tables這個(gè)DMV可以找到有關(guān)該Hash表的一些信息，如圖2所示。這里值得注意的是，當(dāng)執(zhí)行計(jì)劃過(guò)多導(dǎo)致散列后的對(duì)象在同一個(gè)Bucket過(guò)多時(shí)，則需要額外的Bucket，因此可能會(huì)導(dǎo)致查找計(jì)劃緩存效率低下。解決辦法是盡量減少在計(jì)劃緩存中的計(jì)劃個(gè)數(shù)，我們會(huì)在本文后面討論到。

圖2.有關(guān)存儲(chǔ)計(jì)劃緩存的HashTable的相關(guān)信息

當(dāng)出現(xiàn)這類(lèi)問(wèn)題時(shí)，我們可以在buckets_avg_scan_miss_length列看出問(wèn)題。這類(lèi)情況在緩存命中率（SQL Server: Plan Cache-Cache Hit Ratio）比較高，但編譯時(shí)間過(guò)長(zhǎng)時(shí)可以作為考慮對(duì)象。

參數(shù)化和非參數(shù)化

查詢(xún)計(jì)劃的唯一標(biāo)識(shí)是查詢(xún)語(yǔ)句本身，但假設(shè)語(yǔ)句的主體一樣，而僅僅是查詢(xún)條件謂詞不一樣，那在執(zhí)行計(jì)劃中算1個(gè)執(zhí)行計(jì)劃還是兩個(gè)執(zhí)行計(jì)劃呢？It's Depends。

假設(shè)下面兩個(gè)語(yǔ)句,如圖3所示。

圖3.僅僅謂詞條件不一樣的兩個(gè)語(yǔ)句

雖然執(zhí)行計(jì)劃一樣，但是在執(zhí)行計(jì)劃緩存中卻會(huì)保留兩份執(zhí)行計(jì)劃，如圖4所示。

圖4.同一個(gè)語(yǔ)句，不同條件，有兩份不同的執(zhí)行計(jì)劃緩存

我們知道，執(zhí)行計(jì)劃緩存依靠查詢(xún)語(yǔ)句本身來(lái)判別緩存，因此上面兩個(gè)語(yǔ)句在執(zhí)行計(jì)劃緩存中就被視為兩個(gè)不同的語(yǔ)句。那么解決該問(wèn)題的手段就是使得執(zhí)行計(jì)劃緩存中的查詢(xún)語(yǔ)句一模一樣。

參數(shù)化

    使得僅僅是某些參數(shù)不同，而查詢(xún)本身相同的語(yǔ)句可以復(fù)用，就是參數(shù)化的意義所在。比如說(shuō)圖3中的語(yǔ)句，如果我們啟用了數(shù)據(jù)庫(kù)的強(qiáng)制參數(shù)化，或是使用存儲(chǔ)過(guò)程等。SQL Server會(huì)將這些語(yǔ)句強(qiáng)制參數(shù)話(huà)，比如說(shuō)我們根據(jù)圖5修改了數(shù)據(jù)庫(kù)層級(jí)的選項(xiàng)。

圖5.數(shù)據(jù)庫(kù)層級(jí)的選項(xiàng)

此時(shí)我們?cè)賮?lái)執(zhí)行圖3中的兩條語(yǔ)句，通過(guò)查詢(xún)執(zhí)行計(jì)劃緩存，我們發(fā)現(xiàn)變量部分被參數(shù)化了，從而在計(jì)劃緩存中的語(yǔ)句變得一致，如圖6所示，從而可以復(fù)用.

圖6.參數(shù)話(huà)之后的查詢(xún)語(yǔ)句

但是，強(qiáng)制參數(shù)會(huì)引起一些問(wèn)題，查詢(xún)優(yōu)化器很多時(shí)候就無(wú)法根據(jù)統(tǒng)計(jì)信息最優(yōu)化一些具體的查詢(xún)，比如說(shuō)不能應(yīng)用一些索引或者該掃描的時(shí)候卻查找。所產(chǎn)生的負(fù)面影響在上篇文章中已經(jīng)說(shuō)過(guò)，這里就不細(xì)說(shuō)了。

因此對(duì)于上面的問(wèn)題可以有幾種解決辦法。

平衡參數(shù)化和非參數(shù)化

在具體的情況下，參數(shù)化有些時(shí)候是好的,但有些時(shí)候卻是性能問(wèn)題的罪魁禍?zhǔn)祝旅嫖覀儊?lái)看幾種平衡這兩者之間關(guān)系的手段。

使用RECOMPILE

當(dāng)查詢(xún)中，不準(zhǔn)確的執(zhí)行計(jì)劃的成本要高于編譯的成本時(shí)，在存儲(chǔ)過(guò)程中使用RECOMPILE選項(xiàng)或是在即席查詢(xún)中使用RECOMPILE提示使得每次查詢(xún)都會(huì)重新生成執(zhí)行計(jì)劃，該參數(shù)會(huì)使得生成的執(zhí)行計(jì)劃不會(huì)被插入到執(zhí)行計(jì)劃緩存中。對(duì)于OLAP類(lèi)查詢(xún)來(lái)說(shuō)，不準(zhǔn)確的執(zhí)行計(jì)劃所耗費(fèi)的成本往往高于編譯成本太多，所以可以考慮該參數(shù)或選項(xiàng)，您可以如代碼清單1中的查詢(xún)所示這樣使用Hint。

SELECT * FROM Sales.Customer
WHERE CustomerID>20000 AND TerritoryID = 4
OPTION (recompile)

代碼清單1.使用Recompile

除去我們可以手動(dòng)提示SQL Server重編譯之外，SQL Server也會(huì)在下列條件下自動(dòng)重編譯：

元數(shù)據(jù)變更，比如說(shuō)表明稱(chēng)改變、刪除列、變更數(shù)據(jù)類(lèi)型等。
統(tǒng)計(jì)信息變更。

連接的SET參數(shù)變化，SET ANSI_NULLS等的值不一樣，會(huì)導(dǎo)致緩存的執(zhí)行計(jì)劃不能被復(fù)用，從而重編譯。這也是為什么我們看到緩存的執(zhí)行計(jì)劃中語(yǔ)句一模一樣，但就是不復(fù)用，還需要相關(guān)的參數(shù)一致，這些參數(shù)可以通過(guò)sys.dm_exec_plan_attributes來(lái)查看。

使用Optimize For參數(shù)

RECOMPILE方式提供了完全不使用計(jì)劃緩存的節(jié)奏。但有些時(shí)候，特性謂語(yǔ)的執(zhí)行計(jì)劃被使用的次數(shù)h更多，比如說(shuō)，僅僅那些謂語(yǔ)條件產(chǎn)生大量返回結(jié)果集的參數(shù)編譯，我們可以考慮Optimize For參數(shù)。比如我們來(lái)看代碼清單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提示

使用了該參數(shù)會(huì)使得緩存的執(zhí)行計(jì)劃按照OPTIMIZE FOR后面的謂語(yǔ)條件來(lái)生成并緩存執(zhí)行計(jì)劃，這也可能造成不在該參數(shù)中的查詢(xún)效率低下，但是該參數(shù)是我們選擇的，因此通常我們知道哪些謂語(yǔ)條件會(huì)被使用的多一些。

另外，自SQL Server 2008開(kāi)始多了一個(gè)OPTIMIZE FOR UNKNOWN參數(shù)，這使得在優(yōu)化查詢(xún)的過(guò)程中探測(cè)作為謂語(yǔ)條件的局部參數(shù)的值，而不是根據(jù)局部變量的初始值去探測(cè)統(tǒng)計(jì)信息。

在存儲(chǔ)過(guò)程中使用局部變量代替存儲(chǔ)過(guò)程參數(shù)

在存儲(chǔ)過(guò)程中不使用過(guò)程參數(shù)，而是使用局部變量相當(dāng)于直接禁用參數(shù)嗅探。畢竟，局部變量的值只有在運(yùn)行時(shí)才能知道，在執(zhí)行計(jì)劃被查詢(xún)優(yōu)化器編譯時(shí)是無(wú)法知道該值的，因此強(qiáng)迫查詢(xún)分析器使用條件列的平均值進(jìn)行估計(jì)。

雖然這種方式使得參數(shù)估計(jì)變得非常不準(zhǔn)確，但是會(huì)變得非常穩(wěn)定，畢竟統(tǒng)計(jì)信息不會(huì)變更的過(guò)于頻繁。該方式不被推薦,如果可能，盡量使用Optimizer的方式。

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

CREATE PROC TestForLocalVari
@vv INT
AS
DECLARE @vari INT
SET @vari=@vv
SELECT * FROM Sales.Customer
WHERE CustomerIDgt;20000 AND TerritoryID = @vari

代碼清單3.直接引用局部變量，而不是存儲(chǔ)過(guò)程參數(shù)

強(qiáng)制參數(shù)化

在本篇文章的前面已經(jīng)提到過(guò)了強(qiáng)制參數(shù)化，這里就不再提了。

使用計(jì)劃指導(dǎo)

在某些情況下，我們的環(huán)境不允許我們直接修改SQL語(yǔ)句，比如所不希望破壞代碼的邏輯性或是應(yīng)用程序是第三方開(kāi)發(fā)，因此無(wú)論是加HINT或參數(shù)都變得不現(xiàn)實(shí)。此時(shí)我們可以使用計(jì)劃指導(dǎo)。

計(jì)劃指導(dǎo)使得查詢(xún)語(yǔ)句在由客戶(hù)端應(yīng)用程序扔到SQL Server的時(shí)候，SQL Server對(duì)其加上提示或選項(xiàng)，比如說(shuō)通過(guò)代碼清單4可以看到一個(gè)計(jì)劃指導(dǎo)的例子。

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.對(duì)我們前面的查詢(xún)?cè)O(shè)置計(jì)劃指導(dǎo)

當(dāng)加入了計(jì)劃指導(dǎo)后，當(dāng)批處理到達(dá)SQL Server時(shí)，在查找匹配的計(jì)劃緩存時(shí)也會(huì)去找是否有計(jì)劃指導(dǎo)和其相匹配。如果匹配，則應(yīng)用計(jì)劃指導(dǎo)中的提示或選項(xiàng)。這里要注意的是，這里@stmt參數(shù)必須和查詢(xún)語(yǔ)句中的一句一模一樣，差一個(gè)空格都會(huì)被認(rèn)為不匹配。

PARAMETERIZATION SIMPLE

當(dāng)我們?cè)跀?shù)據(jù)庫(kù)層級(jí)啟用了強(qiáng)制參數(shù)化時(shí)，對(duì)于特定語(yǔ)句，我們卻不想啟用強(qiáng)制參數(shù)化，我們可以使用PARAMETERIZATION SIMPLE選項(xiàng)，如代碼清單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.通過(guò)計(jì)劃指南對(duì)單條語(yǔ)句應(yīng)用簡(jiǎn)單參數(shù)化

小結(jié)

執(zhí)行計(jì)劃緩存希望盡量重用執(zhí)行計(jì)劃，這會(huì)減少編譯所消耗的CPU和執(zhí)行緩存所消耗的內(nèi)存。而查詢(xún)優(yōu)化器希望盡量生成更精準(zhǔn)的執(zhí)行計(jì)劃，這勢(shì)必會(huì)造成大量的執(zhí)行計(jì)劃，這不僅僅可能引起重編譯大量消耗CPU，還會(huì)造成內(nèi)存壓力，甚至當(dāng)執(zhí)行計(jì)劃緩存過(guò)多超過(guò)BUCKET的限制時(shí)，在緩存中匹配執(zhí)行計(jì)劃的步驟也會(huì)消耗更多的時(shí)間。

因此利用本篇文章中所述的方法基于實(shí)際的情況平衡兩者之間的關(guān)系，就變得非常重要。