SQL Server內(nèi)存占用的執(zhí)行緩存的正確優(yōu)化
以下的文章主要向大家講述的是正確優(yōu)化SQL Server內(nèi)存占用的執(zhí)行緩存的實(shí)際操作方案，使用參數(shù)化查詢來對(duì)執(zhí)行緩存占用進(jìn)行減少。我們主要是通過如下例子來說明一下正確使用參數(shù)化查詢對(duì)緩存占用的影響。
為方便試驗(yàn)，我們使用了一臺(tái)沒有其它負(fù)載的SQL Server進(jìn)行如下實(shí)驗(yàn)。
下面的腳本循環(huán)執(zhí)行一個(gè)簡(jiǎn)單的查詢，共執(zhí)行10000次。首先，我們清空一下SQL Server已經(jīng)占用的緩存：
dbcc freeproccache然后，執(zhí)行腳本：
- DECLARE @t datetimeSET @t = getdate()SET NOCOUNT ONDECLARE @i INT,
- @count INT, @sql nvarchar(4000)SET @i = 20000WHILE @i <= 30000BEGIN SET
- @sql = 'Select @count=count(*) FROM P_Order Where MobileNo = ' + cast( @i as varchar(10) )
- EXEC sp_executesql @sql ,N'@count INT OUTPUT',
- @count OUTPUT SET @i = @i + 1ENDPRINT DATEDIFF( second, @t, current_timestamp
)輸出：DBCC 執(zhí)行完畢。如果 DBCC 輸出了錯(cuò)誤信息，請(qǐng)與系統(tǒng)管理員聯(lián)系。
11使用了11秒完成10000次查詢。我們看一下SQL Server緩存中所占用的查詢計(jì)劃：Select Count(*) CNT,sum(size_in_bytes) TotalSizeFrom sys.dm_exec_cached_plans查詢結(jié)果：共有2628條執(zhí)行計(jì)劃緩存在SQL Server中。它們所占用的緩存達(dá)到：
92172288字節(jié) = 90012KB = 87 MB。我們也可以使用dbcc memorystatus 命令來檢查SQL Server的執(zhí)行緩存和數(shù)據(jù)緩存占用。執(zhí)行結(jié)果如下：
執(zhí)行緩存占用了90088KB，有2629個(gè)查詢計(jì)劃在緩存里，有1489頁空閑內(nèi)存（每頁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 = 20000WHILE @i <= 30000BEGIN SET
- @sql = 'select @countcount=count(*) FROM P_Order Where MobileNo =
- @i' EXEC sp_executesql @sql, N'@count int output, @i int', @count OUTPUT,
- @i SET @i = @i + 1ENDPRINT DATEDIFF( second, @t, current_timestamp )
輸出：DBCC 執(zhí)行完畢。如果 DBCC 輸出了錯(cuò)誤信息，請(qǐng)與系統(tǒng)管理員聯(lián)系。
1即這次只用1秒鐘即完成了10000次查詢。我們?cè)倏匆幌聅ys.dm_exec_cached_plans中的查詢計(jì)劃：Select Count(*) CNT,sum(size_in_bytes) TotalSize From sys.dm_exec_cached_plans查詢結(jié)果：共有4條執(zhí)行計(jì)劃被緩存。
它們共占用內(nèi)存： 172032字節(jié) = 168KB。如果執(zhí)行dbcc memorystatus，則得到結(jié)果：
有12875頁空閑內(nèi)存(每頁8KB)可以被數(shù)據(jù)緩存所使用。到這里，我們已經(jīng)看到了一個(gè)反差相當(dāng)明顯的結(jié)果。在現(xiàn)實(shí)中，這個(gè)例子中的前者，正是經(jīng)常被使用的一種執(zhí)行SQL腳本的方式（例如：在程序中通過合并字符串方式拼成一條SQL語句，然后通過ADO.NET或者ADO方式傳入SQL Server執(zhí)行）。
解釋一下原因：我們知道，SQL語句在執(zhí)行前首先將被編譯并通過查詢優(yōu)化引擎進(jìn)行優(yōu)化SQL Server內(nèi)存占用，從而得到優(yōu)化后的執(zhí)行計(jì)劃，然后按照?qǐng)?zhí)行計(jì)劃被執(zhí)行。對(duì)于整體相似、僅僅是參數(shù)不同的SQL語句，SQL Server可以重用執(zhí)行計(jì)劃。但對(duì)于不同的SQL語句，SQL Server并不能重復(fù)使用以前的執(zhí)行計(jì)劃，而是需要重新編譯出一個(gè)新的執(zhí)行計(jì)劃。
同時(shí)，SQL Server在內(nèi)存足夠使用的情況下，此時(shí)并不主動(dòng)清除以前保存的查詢計(jì)劃（注：對(duì)于長(zhǎng)時(shí)間不再使用的查詢計(jì)劃，SQL Server也會(huì)定期清理）。這樣，不同的SQL語句執(zhí)行方式，就將會(huì)大大影響SQL Server中存儲(chǔ)的查詢計(jì)劃數(shù)目。
如果限定了SQL Server最大可用內(nèi)存，則過多無用的執(zhí)行計(jì)劃占用，將導(dǎo)致SQL Server可用內(nèi)存減少，從而在執(zhí)行查詢時(shí)尤其是大的查詢時(shí)與磁盤發(fā)生更多的內(nèi)存頁交換。如果沒有限定最大可用內(nèi)存，則SQL Server由于可用內(nèi)存減少，從而會(huì)占用更多內(nèi)存。
對(duì)此，我們一般可以通過兩種方式實(shí)現(xiàn)參數(shù)化查詢：一是盡可能使用存儲(chǔ)過程執(zhí)行SQL語句（這在現(xiàn)實(shí)中已經(jīng)成為SQL Server DBA的一條原則），二是使用sp_executesql 方式執(zhí)行單個(gè)SQL語句（注意不要像上面的第一個(gè)例子那樣使用sp_executesql)。
在現(xiàn)實(shí)的同一個(gè)軟件系統(tǒng)中，大量的負(fù)載類型往往是類似的，所區(qū)別的也只是每次傳入的具體參數(shù)值的不同。所以使用參數(shù)化查詢是必要和可能的。另外，通過這個(gè)例子我們也看到，由于使用了參數(shù)化查詢，不僅僅是優(yōu)化了SQL Server內(nèi)存占用，而且由于能夠重復(fù)使用前面被編譯的執(zhí)行計(jì)劃，使后面的執(zhí)行不需要再次編譯，最終執(zhí)行10000次查詢總共只使用了1秒鐘時(shí)間。
【編輯推薦】
