優(yōu)化SQL Server的內(nèi)存占用之執(zhí)行緩存
更新時(shí)間：2012年04月01日 01:06:07 作者：
在論壇上常見有朋友抱怨，說SQL Server太吃內(nèi)存了。這里筆者根據(jù)經(jīng)驗(yàn)簡(jiǎn)單介紹一下內(nèi)存相關(guān)的調(diào)優(yōu)知識(shí)
首先說明一下SQL Server內(nèi)存占用由哪幾部分組成。SQL Server占用的內(nèi)存主要由三部分組成：數(shù)據(jù)緩存(Data Buffer)、執(zhí)行緩存(Procedure Cache)、以及SQL Server引擎程序。SQL Server引擎程序所占用緩存一般相對(duì)變化不大，則我們進(jìn)行內(nèi)存調(diào)優(yōu)的主要著眼點(diǎn)在數(shù)據(jù)緩存和執(zhí)行緩存的控制上。本文主要介紹一下執(zhí)行緩存的調(diào)優(yōu)。數(shù)據(jù)緩存的調(diào)優(yōu)將在另外的文章中介紹。
對(duì)于減少執(zhí)行緩存的占用，主要可以通過使用參數(shù)化查詢減少內(nèi)存占用。
1、使用參數(shù)化查詢減少執(zhí)行緩存占用
我們通過如下例子來說明一下使用參數(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 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 執(zhí)行完畢。如果 DBCC 輸出了錯(cuò)誤信息，請(qǐng)與系統(tǒng)管理員聯(lián)系。
11
使用了11秒完成10000次查詢。
我們看一下SQL Server緩存中所占用的查詢計(jì)劃：
Select Count(*) CNT,sum(size_in_bytes) TotalSize
From 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é)果如下：
我們現(xiàn)在修改一下前面的腳本，然后重新執(zhí)行一下dbcc freeproccache。再執(zhí)行一遍修改后的腳本：
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 執(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é)果：
到這里，我們已經(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)化，從而得到優(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í)間。
2、檢查并分析SQL Server執(zhí)行緩存中的執(zhí)行計(jì)劃
通過上面的介紹，我們可以看到SQL緩存所占用的內(nèi)存大小。也知道了SQL Server執(zhí)行緩存中的內(nèi)容主要是各種SQL語句的執(zhí)行計(jì)劃。則要對(duì)緩存進(jìn)行優(yōu)化，就可以通過具體分析緩存中的執(zhí)行計(jì)劃，看看哪些是有用的、哪些是無用的執(zhí)行計(jì)劃來分析和定位問題。
通過查詢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
對(duì)于減少執(zhí)行緩存的占用，主要可以通過使用參數(shù)化查詢減少內(nèi)存占用。
1、使用參數(shù)化查詢減少執(zhí)行緩存占用
我們通過如下例子來說明一下使用參數(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í)行腳本：
復(fù)制代碼 代碼如下:
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 執(zhí)行完畢。如果 DBCC 輸出了錯(cuò)誤信息，請(qǐng)與系統(tǒng)管理員聯(lián)系。
11
使用了11秒完成10000次查詢。
我們看一下SQL Server緩存中所占用的查詢計(jì)劃：
Select Count(*) CNT,sum(size_in_bytes) TotalSize
From 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é)果如下：
我們現(xiàn)在修改一下前面的腳本，然后重新執(zhí)行一下dbcc freeproccache。再執(zhí)行一遍修改后的腳本：
復(fù)制代碼 代碼如下:
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 執(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é)果：
到這里，我們已經(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)化，從而得到優(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í)間。
2、檢查并分析SQL Server執(zhí)行緩存中的執(zhí)行計(jì)劃
通過上面的介紹，我們可以看到SQL緩存所占用的內(nèi)存大小。也知道了SQL Server執(zhí)行緩存中的內(nèi)容主要是各種SQL語句的執(zhí)行計(jì)劃。則要對(duì)緩存進(jìn)行優(yōu)化，就可以通過具體分析緩存中的執(zhí)行計(jì)劃，看看哪些是有用的、哪些是無用的執(zhí)行計(jì)劃來分析和定位問題。
通過查詢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
通過緩存計(jì)劃的plan_handle可以查詢到該執(zhí)行計(jì)劃詳細(xì)信息，包括所對(duì)應(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
我們可以選擇針對(duì)那些執(zhí)行計(jì)劃占用較大內(nèi)存、而被重用次數(shù)較少的SQL語句進(jìn)行重點(diǎn)分析?？雌湔{(diào)用方式是否合理。另外，也可以對(duì)執(zhí)行計(jì)劃被重復(fù)使用次數(shù)較多的SQL語句進(jìn)行分析，看其執(zhí)行計(jì)劃是否已經(jīng)經(jīng)過優(yōu)化。進(jìn)一步，通過對(duì)查詢計(jì)劃的分析，還可以根據(jù)需要找到系統(tǒng)中最占用IO、CPU時(shí)間、執(zhí)行次數(shù)最多的一些SQL語句，然后進(jìn)行相應(yīng)的調(diào)優(yōu)分析。篇幅所限，這里不對(duì)此進(jìn)行過多介紹。讀者可以查閱聯(lián)機(jī)叢書中的：sys.dm_exec_query_plan內(nèi)容得到相關(guān)幫助。
附：
1:關(guān)于DBCC MEMORY，可以查看微軟的知識(shí)庫: http://support.microsoft.com/kb/907877/EN-US
2:關(guān)于sys.dm_exec_cached_plans和sys.dm_exec_sql_text，請(qǐng)參閱聯(lián)機(jī)叢書。
您可能感興趣的文章:
- SQL語句實(shí)現(xiàn)查詢SQL Server內(nèi)存使用狀況
- SQL Server 數(shù)據(jù)頁緩沖區(qū)的內(nèi)存瓶頸分析
- SqlServer如何通過SQL語句獲取處理器(CPU)、內(nèi)存（Memory）、磁盤（Disk）以及操作系統(tǒng)相關(guān)信息
- SQL Server 2008 R2占用cpu、內(nèi)存越來越大的兩種解決方法
- 解決SQL Server虛擬內(nèi)存不足情況
- 揭秘SQL Server 2014有哪些新特性(1)-內(nèi)存數(shù)據(jù)庫
- 淺談SQL Server 對(duì)于內(nèi)存的管理[圖文]
- SQL Server內(nèi)存遭遇操作系統(tǒng)進(jìn)程壓榨案例分析
- SQL Server在AlwaysOn中使用內(nèi)存表的“踩坑”記錄
- sql server學(xué)習(xí)基礎(chǔ)之內(nèi)存初探
相關(guān)文章
取值窗口函數(shù)可以用于返回窗口內(nèi)指定位置的數(shù)據(jù)行，本文就主要介紹了SQL 取值窗口函數(shù)的具體使用，具有一定的參考價(jià)值，感興趣的可以了解一下2022-04-04
SQL 中的 substring 函數(shù)是用來抓出一個(gè)欄位資料中的其中一部分。這個(gè)函數(shù)的名稱在不同的資料庫中不完全一樣2009-11-11
SQL事務(wù)用法begin tran，commit tran和rollback tran的用法
Sql Server 2005/2008中提供了begin tran，commit tran和rollback tran來使用事務(wù)。begin tran表示開始事務(wù)， commit tran表示提交事務(wù)，rollback tran表示回滾事物2011-12-12
SQL Server提示"選定的用戶擁有對(duì)象,所以無法除去該用戶”
今天在幫朋友弄一臺(tái)服務(wù)器的時(shí)候當(dāng)我需要?jiǎng)h除一個(gè)數(shù)據(jù)庫里的用戶時(shí)，提示如下錯(cuò)誤信息： "選定的用戶擁有對(duì)象,所以無法除去該用戶" 如何解決呢？2009-04-04
在SQL中我們或許會(huì)使用到局部變量的問題，下面給出微軟的兩個(gè)小例子2010-06-06
asp.net連接查詢SQL數(shù)據(jù)庫并把結(jié)果顯示在網(wǎng)頁上(2種方法)
使用C#連接SQL數(shù)據(jù)庫，并使用SQL語句查詢，摸索了兩天終于運(yùn)行起來了，接下來為大家分享下兩種連接方法，感興趣的朋友可以參考下哈，希望可以幫助到你2013-04-04
深入C++ string.find()函數(shù)的用法總結(jié)
本篇文章是對(duì)C++中string.find()函數(shù)的用法進(jìn)行了詳細(xì)的總結(jié)與分析，需要的朋友參考下2013-05-05
如果想要優(yōu)雅而易于維護(hù)且不容易寫錯(cuò)的代碼，當(dāng)然用高標(biāo)準(zhǔn)的第二種方法。 如果必要考慮風(fēng)險(xiǎn)這個(gè)因素，比如涉及到多種平臺(tái)的遷移或者整合，你應(yīng)該用第一種，起碼在兩個(gè)表的情況下他還是比較安全的。2009-08-08
自動(dòng)化收集SQLSERVER診斷信息的工具選擇及使用介紹
相信很多人都遇到過當(dāng)SQLSERVER出現(xiàn)問題的時(shí)候,如果想解決的話一般需要你收集一些系統(tǒng)信息和SQLSERVER診斷信息；接下來將介紹下工具的選擇及使用，感興趣的你可不要錯(cuò)過了哈，或許本文的知識(shí)點(diǎn)可以幫助到你2013-02-02
將Sql Server對(duì)象的當(dāng)前擁有者更改成目標(biāo)擁有者
將Sql Server對(duì)象的當(dāng)前擁有者更改成目標(biāo)擁有者...2006-10-10