首先說明一下SQL Server內存占用由哪幾部分組成。SQL Server占用的內存主要由三部分組成：數據緩存(Data Buffer)、執行緩存(Procedure Cache)、以及SQL Server引擎程序。SQL Server引擎程序所占用緩存一般相對變化不大，則我們進行內存調優的主要著眼點在數據緩存和執行緩存的控制上。本文主要介紹一下執行緩存的調優。數據緩存的調優將在另外的文章中介紹。
對於減少執行緩存的占用，主要可以通過使用參數化查詢減少內存占用。
1、使用參數化查詢減少執行緩存占用
我們通過如下例子來說明一下使用參數化查詢對緩存占用的影響。為方便試驗，我們使用了一台沒有其它負載的SQL Server進行如下實驗。
下面的腳本循環執行一個簡單的查詢，共執行10000次。
首先，我們清空一下SQL Server已經占用的緩存：
dbcc freeproccache
然後，執行腳本：
復制代碼 代碼如下:
DECLARE @t datetime
SET @t = getdate()
SET NOCOUNT ON
DECLARE @i INT, @count INT, @sql nvarchar(4000)
SET @i = 20000
WHILE @i <= 30000
BEGIN
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 + 1
END
PRINT DATEDIFF( second, @t, current_timestamp )
輸出：
DBCC 執行完畢。如果 DBCC 輸出了錯誤信息，請與系統管理員聯系。
11
使用了11秒完成10000次查詢。
我們看一下SQL Server緩存中所占用的查詢計劃：
Select Count(*) CNT,sum(size_in_bytes) TotalSize
From sys.dm_exec_cached_plans
查詢結果：共有2628條執行計劃緩存在SQL Server中。它們所占用的緩存達到：
92172288字節 = 90012KB = 87 MB。
我們也可以使用dbcc memorystatus 命令來檢查SQL Server的執行緩存和數據緩存占用。
執行結果如下：
執行緩存占用了90088KB，有2629個查詢計劃在緩存裡，有1489頁空閒內存（每頁8KB）可以被數據緩存和其他請求所使用。
我們現在修改一下前面的腳本，然後重新執行一下dbcc freeproccache。再執行一遍修改後的腳本：
復制代碼 代碼如下:
DECLARE @t datetime
SET @t = getdate()
SET NOCOUNT ON
DECLARE @i INT, @count INT, @sql nvarchar(4000)
SET @i = 20000
WHILE @i <= 30000
BEGIN
SET @sql = 'select @count=count(*) FROM P_Order WHERE MobileNo = @i'
EXEC sp_executesql @sql, N'@count int output, @i int', @count OUTPUT, @i
SET @i = @i + 1
END
PRINT DATEDIFF( second, @t, current_timestamp )
輸出：
DBCC 執行完畢。如果 DBCC 輸出了錯誤信息，請與系統管理員聯系。
1
即這次只用1秒鐘即完成了10000次查詢。
我們再看一下sys.dm_exec_cached_plans中的查詢計劃：
Select Count(*) CNT,sum(size_in_bytes) TotalSize From sys.dm_exec_cached_plans
查詢結果：共有4條執行計劃被緩存。它們共占用內存： 172032字節 = 168KB。
如果執行dbcc memorystatus，則得到結果：
有12875頁空閒內存(每頁8KB)可以被數據緩存所使用。
到這裡，我們已經看到了一個反差相當明顯的結果。在現實中，這個例子中的前者，正是經常被使用的一種執行SQL腳本的方式（例如：在程序中通過合並字符串方式拼成一條SQL語句，然後通過ADO.NET或者ADO方式傳入SQL Server執行）。
解釋一下原因：
我們知道，SQL語句在執行前首先將被編譯並通過查詢優化引擎進行優化，從而得到優化後的執行計劃，然後按照執行計劃被執行。對於整體相似、僅僅是參數不同的SQL語句，SQL Server可以重用執行計劃。但對於不同的SQL語句，SQL Server並不能重復使用以前的執行計劃，而是需要重新編譯出一個新的執行計劃。同時，SQL Server在內存足夠使用的情況下，此時並不主動清除以前保存的查詢計劃（注：對於長時間不再使用的查詢計劃，SQL Server也會定期清理）。這樣，不同的SQL語句執行方式，就將會大大影響SQL Server中存儲的查詢計劃數目。如果限定了SQL Server最大可用內存，則過多無用的執行計劃占用，將導致SQL Server可用內存減少，從而在執行查詢時尤其是大的查詢時與磁盤發生更多的內存頁交換。如果沒有限定最大可用內存，則SQL Server由於可用內存減少，從而會占用更多內存。
對此，我們一般可以通過兩種方式實現參數化查詢：一是盡可能使用存儲過程執行SQL語句（這在現實中已經成為SQL Server DBA的一條原則），二是使用sp_executesql 方式執行單個SQL語句（注意不要像上面的第一個例子那樣使用sp_executesql)。
在現實的同一個軟件系統中，大量的負載類型往往是類似的，所區別的也只是每次傳入的具體參數值的不同。所以使用參數化查詢是必要和可能的。另外，通過這個例子我們也看到，由於使用了參數化查詢，不僅僅是優化了SQL Server內存占用，而且由於能夠重復使用前面被編譯的執行計劃，使後面的執行不需要再次編譯，最終執行10000次查詢總共只使用了1秒鐘時間。
2、檢查並分析SQL Server執行緩存中的執行計劃
通過上面的介紹，我們可以看到SQL緩存所占用的內存大小。也知道了SQL Server執行緩存中的內容主要是各種SQL語句的執行計劃。則要對緩存進行優化，就可以通過具體分析緩存中的執行計劃，看看哪些是有用的、哪些是無用的執行計劃來分析和定位問題。
通過查詢DMV: sys.dm_exec_cached_plans,可以了解數據庫中的緩存情況，包括被使用的次數、緩存類型、占用的內存大小等。
SELECT usecounts, cacheobjtype, objtype,size_in_bytes, plan_handle
FROM sys.dm_exec_cached_plans
通過緩存計劃的plan_handle可以查詢到該執行計劃詳細信息，包括所對應的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
我們可以選擇針對那些執行計劃占用較大內存、而被重用次數較少的SQL語句進行重點分析。看其調用方式是否合理。另外，也可以對執行計劃被重復使用次數較多的SQL語句進行分析，看其執行計劃是否已經經過優化。進一步，通過對查詢計劃的分析，還可以根據需要找到系統中最占用IO、CPU時間、執行次數最多的一些SQL語句，然後進行相應的調優分析。篇幅所限，這裡不對此進行過多介紹。讀者可以查閱聯機叢書中的：sys.dm_exec_query_plan內容得到相關幫助。
附：
1:關於DBCC MEMORY，可以查看微軟的知識庫: http://support.microsoft.com/kb/907877/EN-US
2:關於sys.dm_exec_cached_plans和sys.dm_exec_sql_text，請參閱聯機叢書。
