SQL Server 資料頁緩衝區的記憶體瓶頸分析

SQL Server會把經常使用到的資料緩衝在記憶體裡(就是資料頁緩衝)，用以提高資料訪問速度。因為磁碟訪問速度遠遠低於記憶體，所以減少磁碟訪問量同樣是資料庫最佳化的重要方面。

當資料頁緩衝區出現記憶體不足，則會出現查詢慢，磁碟忙等等問題。

分析方法：主要是用到效能計數器。

查看如下效能計數器：

1. SQL SERVER:Buffer Manager-Lazy Writes/sec:記憶體不足則會頻繁調用Lazy Writer把數資料寫入磁碟，此值會經常不為0.

2. SQL SERVER:Buffer Manager-Page life expectancy：記憶體不足時，此計數器表現為下降趨勢或者一直停留在較低值。

3. SQL SERVER:Buffer Manager-Page reads/sec:記憶體不足時，則查詢那些經常使用但又沒有緩衝在記憶體裡的資料時，就不需要讀取磁碟，這此值表現為持續上升或者停留在較高值。

4. SQL SERVER:Buffer Manager-Stolen pages:Stolen pages通常用於緩衝執行計畫，以備重用。記憶體不足時，SQL Server本身機制會優先清除執行計畫緩衝，則此值表現為下降或者較低水平。

查詢目前使用者任務等待：

複製代碼 代碼如下:select * from sys.sysprocesses

如果記憶體不足則，會看到較多的ASYNC_IO_COMPLETION等待類型。這是因為記憶體不足時：a.記憶體和磁碟間會頻繁進行互動,磁碟負載增加 b.需要讀取磁碟上的資料完成查詢，磁碟負載增加。

也就是說這時候磁碟也出現了效能瓶頸，但是這隻是“表面”的，我們要結合多個效能指標來認清根本原因是“記憶體不足”。

確定壓力來源及解決辦法：

通過前的分析，確定了資料頁緩衝相關的記憶體瓶頸。就要分析為什麼會這樣及解決辦法。主要分為如下5個方面：

1. 外部壓力

如果OS層面或者其它應用服務需要更多的記憶體，windows會壓縮Database Pages的記憶體量。這時記憶體壓力來自外部。可以查看如下效能計數器確定是否是外部壓力：

1. SQL Server:Memory Manager-Total Server Memory:此計數器值會下降。

2. Memory:Available Mbytes:此值會下降到較低水平。

3. 在沒有使用AWE或者Lock page in memory前提下，查看Process:Private Bytes-SqlServer和Process：Working Set-SqlServer，兩者值會有顯著下降。

解決方案：如果非DB專用伺服器，則要權衡各個應用服務之間重要性來分配記憶體或者加大記憶體。盡量讓伺服器只運行SQL Server，成為DB專用伺服器。

2. SQL Server自身對Database Page的使用壓力

當Total Server Memory已經達到設定的Max Server Memory或者無法從OS獲得更多記憶體，但是經常訪問的資料量又遠大於實體記憶體用於資料緩衝的容量時，SQL Server被迫將記憶體的資料移入又移出，用於完成當前查詢。

觀察如下效能計數器：

1. SQL Server:Memory Manager-Total Server Memory 和 SQL Server:Memory Manager-Target Server Memory兩者值將會相等。但是前者不會大於後者。

2. 將會出現“分析方法”所述之情況。

解決方案：既然SQL Server沒有足夠記憶體存放Database Page，那就要麼增加SQL Server使用的記憶體量或者減少其使用的記憶體裡。

增加：可以通增加實體記憶體，啟用AWE等方法。

減少：可以通過橫向擴充，有兩台或者多台伺服器分別載部分庫;最佳化相關讀取量較大的語句等。

3. Buffer Pool中的Stolen Memory壓力

正常情況下Buffer Pool中的Stolen Memory不會給Database Pages造成壓力。因為Database Pages有壓力，會觸發Lazy Writes,同時SQL Server 會清理Stolen Memory中的執行計畫緩衝。

但是，如果使用者申明了過多的對象，而沒有登出，並且佔用記憶體過多，就會壓縮Database Pages.如：遊標，自訂引用的執行計畫等。

解決方案：通常是會表現為a)使用者提交的請求因記憶體不足無法完成，701錯誤;b)需要壓縮某些clerk的記憶體量，來完成使用者請求，造成響應延時和緩慢。

通過查詢sys.dm_os_memory_clerks的欄位Single_pages_kb，找出是哪個clerk使用了過多記憶體並分析其原因，然後解決之。

4. Multi-Page的壓力

multi-page跟Buffer Pool共用OS的虛擬位址空間，如果multi-page使用過多記憶體，就會壓縮Datbase pages。multi-page記憶體用量一般較小且相對固定，可能發生的情況有：

a. 未開啟AWE的32位SQL Server只有2G地址空間，且用-g啟動參數擴充的MemToLeave的上限。

b. 64位SQL Server調了記憶體泄露的第三方代碼。

c. 使用帶有大量參數或者較長的”IN”語句

d. 調高了Network Packet Size，大於或等於8KB,並且較多這種串連。

e. 大量複雜XML查詢，或者第三代碼。

解決方案： 通過查詢sys.dm_os_memory_clerks的欄位multi_pages_kb，找出是哪個clerk使用了過多記憶體並分析其原因，然後解決之。

作者：Joe.TJ