SQL Server 記憶體洩露”(memory leak)的案例—遊標導致的記憶體問題

問題描述：客戶反映SQL Server執行一段時間就會報出記憶體不足的錯誤，懷疑是有記憶體洩露。從SQL Server的error log裡面看如下錯誤資訊：

2009-05-14 10:54:20.71 server Error: 17803, Severity: 20, State: 17
2009-05-14 10:54:20.71 server Insufficient memory available..

對於這種記憶體錯誤首先我們應該檢查當前SQL Server的記憶體配置：

1. 32位的SQL Server還是64位的SQL Server？

2. 如果是32位的SQL Server，有沒有啟用AWE的選項。

3. 是否有設定最大伺服器記憶體？

講解這個問題之前需要先介紹一下32位和64位SQL Server在記憶體使用上的不同：

32位的應用程式在32位系統上的記憶體定址空間是2GB的。我們可以使用AWE的方式使SQL Server使用超過2GB的實體記憶體，但是，定址空間依然是2GB。

通過AWE擴充套件出來的記憶體，只可以用來作為資料緩衝區使用。除了資料快取，SQL Server還需要使用記憶體來儲存所有的執行計劃，鎖資源，使用者連線資訊，優化器使用作為評估語句執行計劃的記憶體，語句執行記憶體等等。這些部分加起來不能 超過2GB的記憶體。因此，即使我們為32位的SQL Server擴充套件了記憶體，一旦這2GB的記憶體不夠提供給除了資料快取的其他部分使用，SQL Server依然有面對記憶體不足的問題。本文中討論的記憶體問題就是如此。

這裡提供一篇文件，具體說明了如何為32位的SQL Server擴充套件記憶體：http://support.microsoft.com/default.aspx?scid=kb;en-us;274750

一旦我們使用了AWE選項為SQL Server擴充套件記憶體，我們一定要在sp_configure裡面設定max server memory，以保證OS可以保留足夠的實體記憶體。

我們回到這個記憶體的錯誤，檢查系統的記憶體配置：該系統是32位的SQL Server 2000，啟用了AWE選項，最大伺服器記憶體設定為7500MB。這樣我們有個初步的推斷，問題可能是由於2GB限制以下的某個部分記憶體使用過多導致的。

接下來我們介紹另一個很重要的命令，這個命令在我們處理記憶體問題時經常會使用：

DBCC memorystatus

這個命令是用來輸出當前SQL Server的記憶體使用情況的。在SQL Server 2005以後，我們引入了一個新的DMV，其中包含了更詳細的記憶體分配資訊：sys.dm_os_memory_clerks

在這個問題中，由於系統是SQL Server 2000，所以我們使用dbcc memorystatus來檢視SQL Server的記憶體情況。這裡有兩篇文章分別介紹了SQL 2000和SQL 2005中如何檢視dbcc memorystatus的結果：

http://support.microsoft.com/default.aspx?scid=kb;en-us;271624

http://support.microsoft.com/default.aspx?scid=kb;en-us;907877

我們進一步檢查SQL Server 的error log：

2009-05-06 16:20:22.38 spid215 BPool::Map: no remappable address found.

2009-05-06 16:20:22.46 spid241 BPool::Map: no remappable address found.

2009-05-06 16:20:22.50 spid8 BPool::Map: no remappable address found.

2009-05-06 16:20:22.52 spid242 Buffer Distribution: Stolen=190614 Free=196 Procedures=271

Inram=0 Dirty=104759 Kept=0

I/O=0, Latched=35, ther=664125

2009-05-06 16:20:22.52 spid242 Buffer Counts: Commited=960000 Target=960000 Hashed=768919

InternalReservation=529 ExternalReservation=1426 Min Free=256 Visible= 191224

2009-05-06 16:20:22.52 spid242 Procedure Cache: TotalProcs=67 TotalPages=271 InUsePages=197

2009-05-06 16:20:22.52 spid242 Dynamic Memory Manager: Stolen=190767 OS Reserved=2584

OS Committed=2542

OS In Use=2538

Query Plan=156155 ptimizer=0

General=15253

Utilities=401 Connection=4046

2009-05-06 16:20:22.52 spid242 Global Memory Objects: Resource=9815 Locks=16467

SQLCache=76 Replication=2

LockBytes=2 ServerGlobal=28

Xact=5011

2009-05-06 16:20:22.52 spid242 Query Memory Manager: Grants=11 Waiting=15 Maximum=1512 Available=0

這裡的輸出結果就是DBCC memorystatus的一部分。Buffer Counts: Commited=960000 Target=960000 在這裡的commited的值，是當前buffer pool的大小，target的值是計算出來的buffer pool的大小。如果target的值大於commited的值，說明buffer還要繼續增長，反之，則是buffer pool要收縮。Hashed=768919這個是資料快取的大小，即AWE擴充套件出來的這個部分。我們可以簡單的計算一下，960000*8k，剛好就是 7500MB。其中資料快取是6000MB左右。剩下的部分總共使用了1500MB。

接下來檢視Dynamic memory manager的部分：

Stolen. 是buffer pool中如下5個部分的總和(General, Query Plan, Optimizer, Utilities, Connection). 這個部分的記憶體分配頁面都是小於8KB的。這裡的stole的總和是190767，基本上等於960000-768919的差值。 這說明buffer pool中除去資料快取的部分，剩下的記憶體就都是這5個部分使用了。

在stolen的部分中，我們看到Queryplan 的值非常高，156155*8k=1219MB。Plan cache是用來快取語句的執行計劃的。在32位SQL Server有2GB的記憶體地址的限制情況下，單獨的plan cache使用到了大於1200MB是非常驚人的了，這也是我們這個記憶體問題的根本原因。

接下來我們要研究為什麼這個系統的plan cache會增長到1.2GB。通常情況下，SQL Server會定期的去清除長時間未使用的語句快取，保證plan cache的部分不會漲得過大。我們同樣也提供一個命令去手動的清除plan cache的記憶體：dbcc freeproccache

這個命令執行完以後，會將當前沒有正在被語句使用的快取的執行計劃從SQL Server的記憶體中全部清除。我們在SQL Server上執行dbcc freeproccache命令後，再次使用dbcc memorystatus來檢查queryplan的部分。在這套系統中，我們發現dbcc freeproccache並沒有成功清除掉Queryplan的部分，這個部分依然顯示超過1200MB。這就是為什麼SQL Server也同樣不同清除Queryplan，而導致Queryplan漲到超過1200MB的原因了。

前面我們講過，dbcc freeproccache可以強制清除那些沒有被語句正在使用的執行計劃。如果不能清除，說明這些執行計劃都在被使用中。那麼什麼情況會導致所有的執行 計劃都在被使用中呢？我們聯想到問題的描述是這個記憶體的時候是慢慢增長上來的，那麼這個情況就很有可能是應用程式中遺留了遊標沒有關閉。

檢查系統中的活動遊標，我們引入了另一個命令：DBCC ACTIVECURSORS 這個命令會將當前系統所有未關閉的遊標列印出來：

SPID Cursor Id Pages Stmt

--------------- ------------------------------------------------------------------

55 180150581 2 select * from MESSAGE_DATA where MSG_NUMBER = @P1

55 180150580 2 select mhead.msg_number,customer_id,originator,status,queue,

55 180150577 4 select macc.delivery_time,macc.msg_number,macc.recipient_num

55 180150576 3 select mhis.msg_number,mhis.recipient_number,mhis.update_tim

55 180150568 4 select mh.originator,mh.datatype_id,mh.creation_time,mh.reci

55 180150547 8 select mh.msg_number,mh.orig_msg_number,mh.child_msg_number,

55 180150460 8 select customer_id, company, contact_name, contact_phone, ma

62 180150847 10 select pii.msg_number, pii.item_number, pii.type, pii.amount

62 180150710 10 select pii.msg_number, pii.item_number, pii.type, pii.amount

62 180150661 10 select pii.msg_number, pii.item_number, pii.type, pii.amount

…….

這裡輸出了總共9600多個活動遊標，並且同時輸出了遊標使用的語句。

到目前為止，問題就很清楚了。使用JDBC的應用程式遺漏了某些遊標沒有關係，因此導致這些遊標使用的語句的執行計劃一直無法被SQL Server清除。因此導致了QueryPlan佔用了大量的記憶體，資料庫報出記憶體不足的錯誤。