優化SQL Server的內存占用之履行緩存。本站提示廣大學習愛好者：（優化SQL Server的內存占用之履行緩存）文章只能為提供參考，不一定能成為您想要的結果。以下是優化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，請參閱聯機叢書。
