Performance Monitor3：監控SQL Server的記憶體壓力

標籤：

SQL Server 使用的資源受到作業系統的調度，同時，SQL Server在內部實現了一套調度演算法，用於管理從作業系統擷取的資源，主要是對記憶體和CPU資源的調度。一個好的資料庫系統，必定在記憶體中緩衝足夠多的資訊，以減少從物理硬碟中讀取資料的次數；如果記憶體是系統瓶頸，那麼SQL Server一定會啟動並執行非常慢。監控SQL Server的記憶體壓力，需要從Widnows層級上，對記憶體使用量的整體使用方式進行監控：從SQL Server層級上，監控SQL Server對記憶體資源的使用方式。

一，從Windows層級來監控記憶體資源的使用

作業系統能夠調度的記憶體，有兩個來源：實體記憶體和虛擬記憶體。實體記憶體是記憶體硬體提供的高速訪問裝置，虛擬記憶體是實體記憶體的擴充，作業系統開闢一塊物理Disk空間，作為記憶體空間使用，用於儲存快取資料的檔案，叫作快取檔案（Paging File），路徑名是C:\pagefile.sys，預設是隱藏的。作業系統透明地使用Paging File來儲存資料，Application是無法控制和感知資料是儲存在實體記憶體還是在虛擬記憶體中，即，作業系統決定使用實體記憶體，或Paging file來儲存快取資料。一般，通過Performance Monitor來監控Windows層級的記憶體資源使用方式。

1，監控實體記憶體

常用的系統層級的記憶體計數器跟硬缺頁中斷有關：

• Memory：Page Faults/sec ：每秒發生的Page Fault的數量，Page Fault包括Hard Fault 和 Soft Fault，Hard fault表示需要從Disk中讀取資料頁，Soft fault表示需要從Physical Memory中讀取資料頁，Soft Fault不會影響效能，由於Hard Fault需要訪問Disk，會產生顯著的延遲。
• Memory：Pages Input/sec：每秒發生的Hard Fault的數量，用於計算Hard Fault的百分比： Pages Input / Page Faults = % Hard Page Faults，如果百分比經常大於40%，說明系統需要經常訪問Disk擷取資料，在一定程度上說明系統存在記憶體壓力。
• Memory：Pages/sec：每秒從Disk讀取或寫入Disk的Page數量，表示記憶體和Disk互動的Page的數量：將Page儲存到Disk或從Disk讀取資料到記憶體的Page的數量。

如，Page Faults/sec的數量，均值在6000/s左右，Pages Input/sec波動明顯，時高時低，持續的時間很短，均值在50/s左右，兩者的比例關係均值低於1%，低於40%，可以認為記憶體壓力較小。Pages/sec 和 Pages Input/sec幾乎完全重合，說明，作業系統當時在進行大量的物理讀操作。

2，監控虛擬記憶體

作業系統會同時消耗實體記憶體和虛擬記憶體，虛擬記憶體計數器主要有兩個：

• Paging File：% Usage 用於監控Paging file執行個體的使用比例
• Process: Paging File Bytes  用於監控虛擬記憶體的大小

儲存在虛擬記憶體中的資料越多，說明實體記憶體數量和實際需求量的差距越大，該值僅僅作為參考值，如果長時間接近100%，那麼系統很可能出現異常。

二，從SQL Server層級上，監控SQL Server對記憶體資源的使用方式

1，從Buffer Pool計數器監控伺服器記憶體總體使用方式

由於Buffer Pool是SQL Server記憶體最活躍，使用最多的部分，所以也是最容易出現效能瓶頸的部分，計數值尤其重要：

• Lazy Writes/sec：被LazyWriter重新整理的buffer數量，如果是髒頁，那麼將buffer寫入到Disk，並將buffer空間標記為Free，如果不是髒頁，那麼該buffer空間也被標記為Free，LazyWriter的作用是維護一定數量的Free buffer，SQL Server使用Free buffer來載入新的資料頁。
• Page Life Expectancy：PLE，資料頁駐留在記憶體中的時間。如果SQL Server沒有新的記憶體需求，或有閒置記憶體來完成新的記憶體需求，那麼Lazy Writer不會被處罰，Page會一直駐留在Buffer Pool中，那麼Page Life Expectancy會維持在一個比較高的水平；如果Page Life總是高高低低，表明SQL Server存在記憶體壓力。PLE的參考數值是：Max Server Memory/4GB*300s，如果PLE值長期低於參考值，記憶體可能存在瓶頸。
• Page Reads/sec：每秒從Disk讀取的資料頁數，即物理讀的次數，如果使用者訪問的資料都緩衝在記憶體中，那麼SQL Server不需要從物理Disk上讀取頁面。由於物理IO的開銷大，Page Reads操作一定會影響SQL Server的效能。
• Free list stalls/sec：等待一個Free Page的請求數量，SQL Server申請從Disk載入一個Page到記憶體中，必須在記憶體中分配一個Buffer，Buffer Manager負責維護Free Buffer List，如果Free List沒有任何Free Buffer，那麼請求必須等待，直到有閒置Buffer使用，才能將Disk中的Page載入到記憶體中。

根據圖表資料分析，SQL Server執行大量的物理讀操作，導致PLE大幅降低；從Free List Stall和 Lazy Write的測量值推斷，SQL Server記憶體壓力較小：

• PLE：大幅度降低，從50Ks降低到均值2Ks左右，說明記憶體資料頁被大量替換；
• Free List Stalls/sec： 波動明顯，總體數值很小，說明系統中的Free Buffer能夠滿足SQL Server的需求；
• Lazy Write/sec：均值在4/sec，比較小；
• Page Reads/sec：均值在4000/sec，說明SQL Server在進行大量的物理讀操作

 

BCHR（Buffer cache hit ratio）表示：SQL Server 直接從記憶體中讀取資料的百分比，跟預讀有很大的關係。一次命中意味著在SQL Server讀取資料時，資料存在於記憶體中，跟資料駐留在記憶體中的時間長短，以及記憶體是否有壓力關係不大，僅供參考。

邏輯讀是指直接從記憶體中讀取資料，物理讀是指從物理Disk檔案中載入資料到記憶體，從SQL Server角度來看，BCHR=邏輯讀/（邏輯讀+物理讀）。

如果資料緩衝在記憶體中，那麼SQL Server從記憶體中直接讀取資料，而不需要從物理Disk載入到記憶體。物理Disk能夠執行預讀操作，作業系統將物理Disk上的資料積極式載入到記憶體中，在SQL Server進程訪問資料時，該資料已經存在於記憶體中了。雖然SQL Server申請了物理讀操作，但是，BCHR的測量值沒有體現物理讀操作，這是因為，在SQL Server讀取資料時，資料是存在於記憶體中的，SQL Server執行的是邏輯讀操作。

推薦閱讀《Great SQL Server Debates: Buffer Cache Hit Ratio》：

BCHR only responds to significant memory pressure in conjunction with I/O subsystem pressure, or possibly fragmentation i.e. under conditions that impedes page read-ahead to the point that SQL Server becomes much less effective at populating the data cache with the required pages, before the query processor actually requires them for use.

2，從Memory Manager計數器監控伺服器記憶體總體使用方式

在一個非常繁忙的系統中，Lock記憶體和授予記憶體是常用的計數器：

• Total Server Memory (KB)：SQL Server當前使用的記憶體總量
• Target Server Memory (KB)：SQL Server能夠使用的記憶體總量
• Lock Memory (KB)：SQL Server用於鎖的記憶體總量
• Grant Workspace Memory (KB)：授予記憶體，SQL Server用於執行hash，排序和建立Index操作而消耗的記憶體總量
• Memory Grants Pending (KB)：等待記憶體授與的進程數量，如果進程不能獲得指定數量的記憶體，那麼進程將不會開始執行

 分析圖表，除了Grant Workspace Memory 有變化之外，其餘4個計數值都沒有變化，說明SQL Server執行的操作需要授予記憶體，而Memory Grants Pending 計數值很小，幾乎為0，說明SQL Server 不存在記憶體壓力。

 

結論：記憶體是資料庫系統最重要的資源，作業系統和SQL Server對其的管理比較複雜，從以上計數值的值，基本上能夠推斷出SQL Server是否存在記憶體壓力，可以結合其他測量值進行作證，例如Committed Memory，Stolen Memory，Working Set，Paged Pool，Nonpaged Pool等，這裡就不展開了。

 

拓展閱讀：

Process：Page File Bytes is the current amount of virtual memory, in bytes, that this process has reserved for use in the paging file(s). Paging files are used to store pages of memory used by the process that are not contained in other files. Paging files are shared by all processes, and the lack of space in paging files can prevent other processes from allocating memory. If there is no paging file, this counter reflects the current amount of virtual memory that the process has reserved for use in physical memor

參考doc：

Windows Performance Counters Explained

Buffer cache hit ratio效能計數器真的可以作為記憶體瓶頸的判斷指標嗎？

Great SQL Server Debates: Buffer Cache Hit Ratio

SQL Server memory performance metrics – Part 1 – Memory pages/sec and Memory page faults/sec

Performance Monitor3：監控SQL Server的記憶體壓力