SQL 2008執行語句遇到記憶體不足的案例(error 701)之一

SQL 2008執行語句遇到記憶體不足的案例(error 701)之一

某個特定的存儲過程在SQL

2008中執行會遇到以下錯誤：

Msg 701, Level 17, State 123, Procedure GetAllRevisions_Monthly, Line 22

There is insufficient system memory in resource pool ‘internal’ to run this

query.

Msg 701, Level 17, State 65, Procedure GetAllRevisions_Monthly, Line 22

我們可以從問題的描述和現象中得到什麼資訊：

首先我們需要确認幾個問題：

1.    

這個存儲過程在單獨執行的時候會不會遇到這個錯誤。也就是說，在一個沒有其他使用者通路的時候，單獨執行這個存儲過程。這一點非常重要，可以幫助我們确認SQL

Server是由于總體的工作負載太高而導緻無法配置設定足夠記憶體執行語句，還是這條語句本身執行的問題。

2.    

這個存儲過程是不是每次執行都會遇到這個錯誤。

在這個案例中的情形，該存儲過程即使在單一使用者通路的時候執行，也會遇到這樣的錯誤，并且在每次執行的時候都會出現同樣的錯。需要注意的是，這個存儲過程在第一次執行的時候，會執行一分鐘左右以後，報出錯誤資訊。而當第二次執行和以後多次執行的時候，都是不到一秒立即報錯。

這裡額外的介紹一下對于語句和存儲過程，多次測試的時候需要注意的問題：任何語句在第一次執行的時候，會生成執行計劃并且将執行計劃緩存的SQL

Server的記憶體中，而第二次或者以後多次執行，隻要之前存放的執行計劃沒有從記憶體中清除，語句和存儲過程是會重用原有存儲的執行計劃。是以，如果我們希望每次測試都能實作語句第一次執行時候的效果，我們需要每次執行之前将緩存的執行計劃手工清除。DBCC

freeprocache這個指令可以幫助我們清除所有緩存的執行計劃。

這個案例的測試情況如下：

Dbcc freeproccache

執行存儲過程，用時一分鐘，報錯。

再次執行存儲過程，立刻傳回錯誤。

Dbcc freeproccahe

通過這個現象，可以得出結論，首次執行一分鐘以後報錯，是因為對該存儲過程進行了編譯和生成執行計劃。而後續執行時立即報錯，是重用了存儲過程緩存的執行計劃。也就是說，這個存儲過程的解析和編譯過程是沒有問題的。但是一旦按照編譯的執行計劃執行的時候，就遇到了記憶體不足的問題。

以上是我們通過問題的現象和描述分析出問題的可能性。接下來我們就進入重制問題收集log的階段。

對于記憶體不足的錯誤，我們需要收集哪些資訊用以診斷呢？

Windows性能螢幕日志：

SQLServer:Memory

Memory  —這個object描述的是windows的記憶體配置設定和可用記憶體情況

SQL Server 動态性能視圖：

sys.dm_exec_query_memory_grants

sys.dm_os_memory_clerks

dbcc

memorystatus

如何收集這些資訊？

Windows性能螢幕日志，隻需要在windows性能螢幕裡面自定義一個使用者收集結果集，然後将objectSQLServer:Memory添加進去就可以了。需要主要的是設定收集間隔，由于這個錯誤在語句開始執行後很快就會出現，是以，将收集間隔設定的過大時是難以準确收集到問題出現時的記憶體異常情況，我們建議将收集間隔設定為1-2秒。

收集SQL

Server的動态性能視圖的方法類似，我們可以使用如下腳本每隔一秒打出一次動态性能視圖的結果集合SQL

Server的記憶體情況：

while(1=1)

begin

print getdate()

print

‘*****sys.dm_os_memory_clerks******’

select * from

‘*****sys.dm_exec_query_memory_grants******’

print ‘DBCC memorystatus’

dbcc memorystatus

waitfor delay ’00:00:01′

end

在management studio中執行這個腳本，這個腳本會每隔一秒列印一次兩個view的資訊和dbcc

指令的輸出。我們建議設定目前的查詢結果輸出的檔案的方式來輸出這個腳本的結果。

如何為這個問題收集資訊？

現在我們已經知道了如何重制這個問題，也知道了對于這個問題應該收集什麼資訊來檢查，接下來的問題是，如何收集資訊？我們需要在問題出現之前，先講兩個部分資訊收集啟動：啟動我們自己配置的windows

性能螢幕日志，啟動腳本。接下來，我們可以在management

studio裡面執行語句并且重制問題了。

資訊的分析

1.     問題出現之前，腳本打出來的dbcc memorystatus：

Current Buffer Pool Stats

———————————–

Total Buffers=1048576 (8192MB)

Max Committed=640000 (5000MB)

Committed Target=640000 (5000MB)

Committed=41344 (323MB)

Hashed=6255 (48MB)

Free=17746 (138MB)

Stolen=17343 (135MB)

Reserved=590656 (4614MB)

OutOfMemory=false

WaitingForRM=false

Failed to complete calculation of

statistics!                       

Latched=0 (0MB)

Dirty=0 (0MB)

In IO=0 (0MB)

Stolen potential=1 (0MB)

這個資訊如何閱讀？committed

target表示SQL

Server可以為buffer

pool配置設定的空間，64000是以8k的page為機關的，是以640000計算出來時5000MB。Committed表示目前已經使用的buffer

pool的大小，41344計算出來時323MB。這個資訊告訴我們，在問題出現之前，該系統的buffer pool裡面是還有超過4677MB的空閑記憶體。

2.     Windows event log：

在診斷記憶體問題的時候，首先需要确認的就是，該記憶體問題是由于windows的記憶體缺乏導緻的還是SQL Server自身的記憶體問題導緻的。當windows遇到記憶體壓力，沒有可用記憶體的時候，OS被強制在其上運作的所有應用程式釋放實體記憶體。SQL

Server在這種情況下，由于buffer pool短時間之内需要釋放大量記憶體并且急劇縮小大小，當時在SQL

Server上執行的語句也會報出錯誤701，沒有足夠的記憶體執行語句。但是這個記憶體錯誤的本身是由于windows

強制收回記憶體導緻的。我們會在另一個案例中詳細讨論這個問題。

我們檢查OS的可用記憶體，memory：available memory(MB),在這個案例中，問題發生之前和問題發生的時候，windows依然保持5.7GB的記憶體。是以這裡的記憶體問題是SQL

Server内部産生的，并不是作業系統和伺服器的記憶體缺乏問題。

接下來我們檢查SQLServer:Memory

下面的Granted workspace

memory(KB)，這個值代辦SQL

Server配置設定出來執行語句的記憶體，主要是用來做排序，哈希連接配接等等。

通過這個event，我們發現這裡有兩次巨大的記憶體配置設定(我們測試的時候執行了兩次存儲過程)，這裡的最大值為4617MB。基本上接近了buffer

pool中所有的空閑記憶體。

我們比較在腳本中收集到的sys.dm_exec_query_memory_grants的資訊：

session_id dop request_time             grant_time              

requested_memory_kb

granted_memory_kb required_memory_kb

———- — ————————

———————— ——————- —————–

——————

53         16  02/19/2010 17:13:02.540 

02/19/2010 17:13:02.540  4728392             4728392          

4728392       

配置設定的數值和時間上完全吻合。  

如果我們遇到的問題是，發現有大量的記憶體配置設定給了使用者session，但是我們不知道使用者session在當時執行的語句是什麼，如何通過動态性能視圖來定位語句呢？在sys.dm_exec_query_memory_grants中包含了一個sql_handle的列，我們可以通過這個列去連接配接：

Select *,text, query_plan from FROM

CROSS APPLY sys.dm_exec_sql_text(sql_handle)

CROSS APPLY sys.dm_exec_query_plan

(plan_handle)

這樣我們就可以直接通過動态性能視圖得到了SQL語句和該SQL語句使用的XML格式的執行計劃了。我們将XML格式的執行計劃另存為.sqlplan的字尾的檔案，然後在management

studio中打開，可以将其展開為圖形界面的執行計劃。如果不熟悉使用XML格式的執行計劃，這種方法可以簡化檢查執行計劃的工作。

問題分析到這裡，基本上我們就可以定位出原因了，确實是因為這個存儲過程執行時，SQL Server預估的記憶體過大，導緻現有記憶體不足以支援存儲過程的執行。

解決的方法：

接下來，我們就可以檢視XML的執行計劃，在這裡，我們發現SQL Server對這個存儲過程使用的并行度為16的執行方式。對于使用并行的語句，SQL Server面對的兩個額外的開銷：

a.  更多的記憶體配置設定

b.  更多的CPU資源消耗。

是以，一旦我們确認記憶體使用過多或者CPU使用率過高是跟并行執行有關，我們需要對SQLServer手工設定一個較低的最大并行度參數：

sp_configure ‘show advanced options’,1

reconfigure with

override

go

sp_configure ‘max degree of parallelism’,4

reconfigure with override

上述指令中第一條是用來打開進階選項的，第二條是将最大并行度設定為4.以上設定不需要重新開機SQL Server服務。關于最大并行度的參數，我們建議設定為CPU數或者CPU數的一半。在多于32個CPU的系統中，我們建議設定這個值為4或者8.

額外的問題：

到目前為止，原因已經非常清楚了，而且我們得到的解決問題的方法。這裡有一個疑問，為什麼SQL Server會在明知道沒有這麼多可用記憶體的情況下，去生成一個需要這麼多記憶體的執行計劃呢？并且還不斷的重用這個不能執行的執行計劃？這樣是SQL Server的産品設計有問題嗎？

其實這個問題的出現是一個很巧合的情況。我們根據SQL Server的dump來分析，發現這個語句在評估和生成執行計劃的時候，計算出來所需要的記憶體接近4670MB，當時buffer

pool裡面還有4677MB的記憶體，是以評估出來的執行計劃雖然接近了零界值，但是依然是能夠滿足執行計劃的需要的。而當SQL語句真正按照執行計劃執行的時候，其實還有一些少量的記憶體的額外開銷，正是因為加上了這些額外的開銷，終于超出了剩餘的4677MB的記憶體限制。在這種情況下，SQL

Server每次對執行計劃的評估和緩存都成功了，而執行的時候才會報錯。這也就是為什麼下次執行的時候該存儲過程還是會重用緩存的執行計劃。當語句到執行階段的時候，不管成功不成功，都不會回頭改寫緩存的執行計劃，是以SQL server就不斷的為這個存儲過程重用同樣的執行計劃，直到我們将最大并行度降為4，SQL

Server生成新的并行度為4的執行計劃以後，該存儲過程就能正确的運作了。

<a href="https://blogs.msdn.microsoft.com/apgcdsd/2011/01/16/sql-2008error-701/">https://blogs.msdn.microsoft.com/apgcdsd/2011/01/16/sql-2008error-701/</a>