對于減少執(zhí)行緩存的占用，主要可以通過使用參數(shù)化查詢減少內(nèi)存占用。
1、使用參數(shù)化查詢減少執(zhí)行緩存占用
我們通過如下例子來說明一下使用參數(shù)化查詢對緩存占用的影響。為方便試驗，我們使用了一臺沒有其它負載的SQL Server進行如下實驗。
下面的腳本循環(huán)執(zhí)行一個簡單的查詢，共執(zhí)行10000次。 首先，我們清空一下SQL Server已經(jīng)占用的緩存：
dbcc freeproccache 然后，執(zhí)行腳本：

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

執(zhí)行緩存占用了90088KB，有2629個查詢計劃在緩存里，有1489頁空閑內(nèi)存（每頁8KB）可以被數(shù)據(jù)緩存和其他請求所使用。 我們現(xiàn)在修改一下前面的腳本，然后重新執(zhí)行一下dbcc freeproccache。再執(zhí)行一遍修改后的腳本：

輸出：
DBCC 執(zhí)行完畢。如果 DBCC 輸出了錯誤信息，請與系統(tǒng)管理員聯(lián)系。
1
即這次只用1秒鐘即完成了10000次查詢。
我們再看一下sys.dm_exec_cached_plans中的查詢計劃：
Select Count(*) CNT,sum(size_in_bytes) TotalSize From sys.dm_exec_cached_plans 查詢結(jié)果：共有4條執(zhí)行計劃被緩存。它們共占用內(nèi)存： 172032字節(jié) = 168KB。
如果執(zhí)行dbcc memorystatus，則得到結(jié)果：

有12875頁空閑內(nèi)存(每頁8KB)可以被數(shù)據(jù)緩存所使用。 到這里，我們已經(jīng)看到了一個反差相當(dāng)明顯的結(jié)果。在現(xiàn)實中，這個例子中的前者，正是經(jīng)常被使用的一種執(zhí)行SQL腳本的方式（例如：在程序中通過合并字符串方式拼成一條SQL語句，然后通過ADO.NET或者ADO方式傳入SQL Server執(zhí)行）。 解釋一下原因：
我們知道，SQL語句在執(zhí)行前首先將被編譯并通過查詢優(yōu)化引擎進行優(yōu)化，從而得到優(yōu)化后的執(zhí)行計劃，然后按照執(zhí)行計劃被執(zhí)行。對于整體相似、僅僅是參數(shù)不同的SQL語句，SQL Server可以重用執(zhí)行計劃。但對于不同的SQL語句，SQL Server并不能重復(fù)使用以前的執(zhí)行計劃，而是需要重新編譯出一個新的執(zhí)行計劃。同時，SQL Server在內(nèi)存足夠使用的情況下，此時并不主動清除以前保存的查詢計劃（注：對于長時間不再使用的查詢計劃，SQL Server也會定期清理）。這樣，不同的SQL語句執(zhí)行方式，就將會大大影響SQL Server中存儲的查詢計劃數(shù)目。如果限定了SQL Server最大可用內(nèi)存，則過多無用的執(zhí)行計劃占用，將導(dǎo)致SQL Server可用內(nèi)存減少，從而在執(zhí)行查詢時尤其是大的查詢時與磁盤發(fā)生更多的內(nèi)存頁交換。如果沒有限定最大可用內(nèi)存，則SQL Server由于可用內(nèi)存減少，從而會占用更多內(nèi)存。 對此，我們一般可以通過兩種方式實現(xiàn)參數(shù)化查詢：一是盡可能使用存儲過程執(zhí)行SQL語句（這在現(xiàn)實中已經(jīng)成為SQL Server DBA的一條原則），二是使用sp_executesql 方式執(zhí)行單個SQL語句（注意不要像上面的第一個例子那樣使用sp_executesql)。 在現(xiàn)實的同一個軟件系統(tǒng)中，大量的負載類型往往是類似的，所區(qū)別的也只是每次傳入的具體參數(shù)值的不同。所以使用參數(shù)化查詢是必要和可能的。另外，通過這個例子我們也看到，由于使用了參數(shù)化查詢，不僅僅是優(yōu)化了SQL Server內(nèi)存占用，而且由于能夠重復(fù)使用前面被編譯的執(zhí)行計劃，使后面的執(zhí)行不需要再次編譯，最終執(zhí)行10000次查詢總共只使用了1秒鐘時間。 2、檢查并分析SQL Server執(zhí)行緩存中的執(zhí)行計劃
通過上面的介紹，我們可以看到SQL緩存所占用的內(nèi)存大小。也知道了SQL Server執(zhí)行緩存中的內(nèi)容主要是各種SQL語句的執(zhí)行計劃。則要對緩存進行優(yōu)化，就可以通過具體分析緩存中的執(zhí)行計劃，看看哪些是有用的、哪些是無用的執(zhí)行計劃來分析和定位問題。 通過查詢DMV: sys.dm_exec_cached_plans,可以了解數(shù)據(jù)庫中的緩存情況，包括被使用的次數(shù)、緩存類型、占用的內(nèi)存大小等。
SELECT usecounts, cacheobjtype, objtype,size_in_bytes, plan_handle
FROM sys.dm_exec_cached_plans

通過緩存計劃的plan_handle可以查詢到該執(zhí)行計劃詳細信息，包括所對應(yīng)的SQL語句：

SELECT TOP 100 usecounts,

objtype,

p.size_in_bytes,

[sql].[text]

FROM sys.dm_exec_cached_plans p

OUTER APPLY sys.dm_exec_sql_text (p.plan_handle) sql

ORDER BY usecounts

我們可以選擇針對那些執(zhí)行計劃占用較大內(nèi)存、而被重用次數(shù)較少的SQL語句進行重點分析?？雌湔{(diào)用方式是否合理。另外，也可以對執(zhí)行計劃被重復(fù)使用次數(shù)較多的SQL語句進行分析，看其執(zhí)行計劃是否已經(jīng)經(jīng)過優(yōu)化。進一步，通過對查詢計劃的分析，還可以根據(jù)需要找到系統(tǒng)中最占用IO、CPU時間、執(zhí)行次數(shù)最多的一些SQL語句，然后進行相應(yīng)的調(diào)優(yōu)分析。篇幅所限，這里不對此進行過多介紹。讀者可以查閱聯(lián)機叢書中的：sys.dm_exec_query_plan內(nèi)容得到相關(guān)幫助。

附：

1:關(guān)于DBCC MEMORY，可以查看微軟的知識庫: http://support.microsoft.com/kb/907877/EN-US

2:關(guān)于sys.dm_exec_cached_plans和sys.dm_exec_sql_text，請參閱聯(lián)機叢書。

版權(quán)聲明：本站文章來源標注為YINGSOO的內(nèi)容版權(quán)均為本站所有，歡迎引用、轉(zhuǎn)載，請保持原文完整并注明來源及原文鏈接。禁止復(fù)制或仿造本網(wǎng)站，禁止在非www.sddonglingsh.com所屬的服務(wù)器上建立鏡像，否則將依法追究法律責(zé)任。本站部分內(nèi)容來源于網(wǎng)友推薦、互聯(lián)網(wǎng)收集整理而來，僅供學(xué)習(xí)參考，不代表本站立場，如有內(nèi)容涉嫌侵權(quán)，請聯(lián)系alex-e#qq.com處理。