最佳化SQL Server的記憶體佔用之執行緩衝

首先說明一下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，請參閱聯機叢書。