優(yōu)化SQL Server的內(nèi)存占用之執(zhí)行緩存

1、窗體頂端優(yōu)化SQL Server的內(nèi)存占用之執(zhí)行緩存篇優(yōu)化SQL Server的內(nèi)存占用之執(zhí)行緩存 在論壇上常見(jiàn)有朋友抱怨，說(shuō)SQL Server太吃?xún)?nèi)存了。這里筆者根據(jù)經(jīng)驗(yàn)簡(jiǎn)單介紹一下內(nèi)存相關(guān)的調(diào)優(yōu)知識(shí)。首先說(shuō)明一下SQL Server內(nèi)存占用由哪幾部分組成。SQL Server占用的內(nèi)存主要由三部分組成：數(shù)據(jù)緩存(Data Buffer、執(zhí)行緩存(Procedure Cache、以及SQL Server引擎程序。SQL Server引擎程序所占用緩存一般相對(duì)變化不大，則我們進(jìn)行內(nèi)存調(diào)優(yōu)的主要著眼點(diǎn)在數(shù)據(jù)緩存和執(zhí)行緩存的控制上。本文主要介紹一下執(zhí)行緩存的調(diào)優(yōu)。數(shù)據(jù)緩存的調(diào)優(yōu)將在另外

2、的文章中介紹。 對(duì)于減少執(zhí)行緩存的占用，主要可以通過(guò)使用參數(shù)化查詢(xún)減少內(nèi)存占用。1、使用參數(shù)化查詢(xún)減少執(zhí)行緩存占用我們通過(guò)如下例子來(lái)說(shuō)明一下使用參數(shù)化查詢(xún)對(duì)緩存占用的影響。為方便試驗(yàn)，我們使用了一臺(tái)沒(méi)有其它負(fù)載的SQL Server進(jìn)行如下實(shí)驗(yàn)。下面的腳本循環(huán)執(zhí)行一個(gè)簡(jiǎn)單的查詢(xún)，共執(zhí)行10000次。 首先，我們清空一下SQL Server已經(jīng)占用的緩存：dbcc freeproccache 然后，執(zhí)行腳本：DECLARE t datetimeSET t = getdate(SET NOCOUNT

3、 ONDECLARE i INT, count INT, sql nvarchar(4000 SET i = 20000WHILE i <= 30000BEGIN    SET sql = 'SELECT count=count(* FROM P_Order WHERE MobileNo = ' + cast( ias var

4、char(10     EXEC sp_executesql sql ,N'count INT OUTPUT', count OUTPUT    SET i = i + 1ENDPRINT DATEDIFF( second, t, current_timestamp  輸出：DBCC 執(zhí)行完畢。如果 DBCC&#

5、160;輸出了錯(cuò)誤信息，請(qǐng)與系統(tǒng)管理員聯(lián)系。11 使用了11秒完成10000次查詢(xún)。我們看一下SQL Server緩存中所占用的查詢(xún)計(jì)劃：Select Count(* CNT,sum(size_in_bytes TotalSizeFrom  查詢(xún)結(jié)果：共有2628條執(zhí)行計(jì)劃緩存在SQL Server中。它們所占用的緩存達(dá)到：92172288字節(jié) = 90012KB = 87 MB。 我們也可以使用dbcc memorystatus 命令來(lái)檢查SQL Server的執(zhí)行緩存和數(shù)據(jù)緩存占用。執(zhí)行結(jié)果如下：&#

6、160;執(zhí)行緩存占用了90088KB，有2629個(gè)查詢(xún)計(jì)劃在緩存里，有1489頁(yè)空閑內(nèi)存（每頁(yè)8KB）可以被數(shù)據(jù)緩存和其他請(qǐng)求所使用。 我們現(xiàn)在修改一下前面的腳本，然后重新執(zhí)行一下dbcc freeproccache。再執(zhí)行一遍修改后的腳本：DECLARE t datetimeSET t = getdate(SET NOCOUNT ONDECLARE i INT, count INT, sql nvarchar(4000 SET i&#

7、160;= 20000WHILE i <= 30000BEGIN    SET sql = 'select count=count(* FROM P_Order WHERE MobileNo = i'    EXEC sp_executesql sql, N'count int output, i int', count OUTPUT, i 

8、;   SET i = i + 1ENDPRINT DATEDIFF( second, t, current_timestamp  輸出：DBCC 執(zhí)行完畢。如果 DBCC 輸出了錯(cuò)誤信息，請(qǐng)與系統(tǒng)管理員聯(lián)系。1即這次只用1秒鐘即完成了10000次查詢(xún)。我們?cè)倏匆幌轮械牟樵?xún)計(jì)劃： 查詢(xún)結(jié)果：共有4條執(zhí)行計(jì)劃被緩存。它們共占用內(nèi)存： 172032字節(jié) = 168KB。如果執(zhí)行dbcc memorys

9、tatus，則得到結(jié)果：有12875頁(yè)空閑內(nèi)存(每頁(yè)8KB可以被數(shù)據(jù)緩存所使用。 到這里，我們已經(jīng)看到了一個(gè)反差相當(dāng)明顯的結(jié)果。在現(xiàn)實(shí)中，這個(gè)例子中的前者，正是經(jīng)常被使用的一種執(zhí)行SQL腳本的方式（例如：在程序中通過(guò)合并字符串方式拼成一條SQL語(yǔ)句，然后通過(guò)或者ADO方式傳入SQL Server執(zhí)行）。 解釋一下原因：我們知道，SQL語(yǔ)句在執(zhí)行前首先將被編譯并通過(guò)查詢(xún)優(yōu)化引擎進(jìn)行優(yōu)化，從而得到優(yōu)化后的執(zhí)行計(jì)劃，然后按照?qǐng)?zhí)行計(jì)劃被執(zhí)行。對(duì)于整體相似、僅僅是參數(shù)不同的SQL語(yǔ)句，SQL Server可以重用執(zhí)行計(jì)劃。但對(duì)于不同的SQL語(yǔ)句，SQL Server并不能重復(fù)使用以前

10、的執(zhí)行計(jì)劃，而是需要重新編譯出一個(gè)新的執(zhí)行計(jì)劃。同時(shí)，SQL Server在內(nèi)存足夠使用的情況下，此時(shí)并不主動(dòng)清除以前保存的查詢(xún)計(jì)劃（注：對(duì)于長(zhǎng)時(shí)間不再使用的查詢(xún)計(jì)劃，SQL Server也會(huì)定期清理）。這樣，不同的SQL語(yǔ)句執(zhí)行方式，就將會(huì)大大影響SQL Server中存儲(chǔ)的查詢(xún)計(jì)劃數(shù)目。如果限定了SQL Server最大可用內(nèi)存，則過(guò)多無(wú)用的執(zhí)行計(jì)劃占用，將導(dǎo)致SQL Server可用內(nèi)存減少，從而在執(zhí)行查詢(xún)時(shí)尤其是大的查詢(xún)時(shí)與磁盤(pán)發(fā)生更多的內(nèi)存頁(yè)交換。如果沒(méi)有限定最大可用內(nèi)存，則SQL Server由于可用內(nèi)存減少，從而會(huì)占用更多內(nèi)存。 對(duì)此，我們一般可以通過(guò)兩種方式實(shí)現(xiàn)參數(shù)化

11、查詢(xún)：一是盡可能使用存儲(chǔ)過(guò)程執(zhí)行SQL語(yǔ)句（這在現(xiàn)實(shí)中已經(jīng)成為SQL Server DBA的一條原則），二是使用sp_executesql 方式執(zhí)行單個(gè)SQL語(yǔ)句（注意不要像上面的第一個(gè)例子那樣使用sp_executesql。 在現(xiàn)實(shí)的同一個(gè)軟件系統(tǒng)中，大量的負(fù)載類(lèi)型往往是類(lèi)似的，所區(qū)別的也只是每次傳入的具體參數(shù)值的不同。所以使用參數(shù)化查詢(xún)是必要和可能的。另外，通過(guò)這個(gè)例子我們也看到，由于使用了參數(shù)化查詢(xún)，不僅僅是優(yōu)化了SQL Server內(nèi)存占用，而且由于能夠重復(fù)使用前面被編譯的執(zhí)行計(jì)劃，使后面的執(zhí)行不需要再次編譯，最終執(zhí)行10000次查詢(xún)總共只使用了1秒鐘時(shí)間。

12、0;2、檢查并分析SQL Server執(zhí)行緩存中的執(zhí)行計(jì)劃通過(guò)上面的介紹，我們可以看到SQL緩存所占用的內(nèi)存大小。也知道了SQL Server執(zhí)行緩存中的內(nèi)容主要是各種SQL語(yǔ)句的執(zhí)行計(jì)劃。則要對(duì)緩存進(jìn)行優(yōu)化，就可以通過(guò)具體分析緩存中的執(zhí)行計(jì)劃，看看哪些是有用的、哪些是無(wú)用的執(zhí)行計(jì)劃來(lái)分析和定位問(wèn)題。 通過(guò)查詢(xún)DMV: sys.dm_exec_cached_plans,可以了解數(shù)據(jù)庫(kù)中的緩存情況，包括被使用的次數(shù)、緩存類(lèi)型、占用的內(nèi)存大小等。SELECT usecounts, cacheobjtype, objtype,size_in_bytes,

13、60;plan_handleFROM  通過(guò)緩存計(jì)劃的plan_handle可以查詢(xún)到該執(zhí)行計(jì)劃詳細(xì)信息，包括所對(duì)應(yīng)的SQL語(yǔ)句：SELECT  TOP 100 usecounts,    objtype,    p.size_in_bytes,    sql.textFROM  pOUTER APPLY sys.dm_exec_sql_text (p.plan_handle sqlORDER BY usecounts 我們可以選擇針對(duì)那些執(zhí)行計(jì)劃占用較大內(nèi)存、而被重用次數(shù)較少的SQL語(yǔ)句進(jìn)行重點(diǎn)分析。看其調(diào)用方式是否合理。另外，也可以