人人都是 DBA（IV）SQL Server 記憶體管理

標籤：des   style   blog   http   ar   color   os   使用   sp   

SQL Server 的記憶體管理是一個龐大的主題，涉及特別多的概念和技術，例如常見的 Plan Cache、Buffer Pool、Memory Clerks 等。本文僅是管中窺豹，描述常見的記憶體管理相關概念。

在瞭解記憶體管理之前，通過 sys.dm_os_memory_clerks 視圖可以查詢記憶體的使用職責（Memory Clerks），也就是記憶體的消耗者。

SELECT [type], SUM(pages_kb) AS total_pages_kbFROM sys.dm_os_memory_clerksWHERE pages_kb != 0GROUP BY [type]ORDER BY total_pages_kb DESC;

SQL Server 中最主要的記憶體組件就是緩衝池（Buffer Pool, or Bpool）。緩衝池是一個 8KB 頁面的集合，任何大於 8KB 的記憶體塊都需要進行單獨分配管理，例如 COM Objects、CLR Code、Extended Stored Procedures、Large Cached Plan 等。

緩衝區管理器（Buffer Manager）負責從磁碟上的資料檔案中讀取資料頁（Data Page）和索引頁（Index Page），將頁資料放入 Buffer Pool 中作為資料緩衝（Data Cache）。Buffer 指的是記憶體中的一個 Page，該 Page 的大小與資料頁或索引頁相同。

SQL Server 採用預讀機制（Read-ahead）從磁碟中讀取資料頁和索引頁至緩衝區中，以提高效能。預讀機制完全有系統內部控制，不需要進行任何配置和調整。

資料緩衝（Data Cache）

資料緩衝（Data Cache）中使用雜湊（Hashing）方式儲存頁資料。比如給定 DatabaseID-FileNumber-PageNumber 標識符，通過雜湊函數將標識符存放到雜湊表中。這樣，通過雜湊表（Hash Table）提供的快速檢索功能，資料庫引擎僅需要較少的記憶體讀取就可以判斷目標頁是否存在於緩衝中，如果不存在再從磁碟中讀入緩衝。

SQL Server 會將那些一段時間未被引用的頁面緩衝位置標記為空白閑頁，通過空閑頁鏈表進行管理。當需要新的緩衝頁時，將從鏈表頭擷取要使用的頁地址。

每個資料緩衝的頁中都包含一塊表頭地區，包含該頁最後兩次被引用的相關資訊和狀態資訊，例如描述該頁是否為髒頁（Dirty Page）。髒頁（Dirty Page）指的是從磁碟讀取後的資料頁被修改過。引用資訊則用於實現資料快取頁面的頁面替換策略，它使用 LRU-K 演算法。LRU-K 演算法將有價值的緩衝區持有頁保留在活躍緩衝池中，而如果緩衝區持有頁的引用頻率不高，則這些緩衝區頁將被逐漸地釋放回空閑緩衝區列表中。

LazyWriter 線程

SQL Server 為每個 NUMA 節點都建立了一個 LazyWriter 線程，用於掃描與該節點關聯的緩衝區。LazyWriter 線程會進行周期性的睡眠和喚醒，當喚醒時將檢查空閑緩衝區列表的長度，如果低於某個閾值，將掃描整個緩衝區。在掃描過程中，當發現頁的引用率較低時，將檢查髒頁指示符。如果該頁是髒頁，則執行磁碟寫入操作。然後該頁將被釋放回空閑緩衝區列表中。

SQL Server 動態使用記憶體時，必須不斷地偵聽可用記憶體的數量，並且追蹤和監視記憶體的更改，以判斷何時增加或減少自身的總記憶體量。每當 SQL Server 中的記憶體增加或減少 1MB，或達到伺服器記憶體的 5% 時，將會產生事件通知。

檢查點（Checkpoint）

檢查點（Checkpoint）線程也定期掃描緩衝區，並將髒資料頁寫入磁碟。檢查點與 LazyWriter 的區別在於，檢查點不會向空閑緩衝區列表添加空閑緩衝區。檢查點的唯一目的就是，確保將某一時刻前的頁面都寫入磁碟中，以便始終保持記憶體中的髒頁數量最小。

SQL Server 將檢查點的運行過程記錄到交易記錄中，當 SQL Server 出現故障時，由於已寫入了某一時刻前的資料，可以減少恢復。

觸發檢查點的情況有：

• 手動觸發 CHECKPOINT 命令在指定資料庫上執行檢查點。
• 日誌正在變慢，超過容量的 70%。觸發檢查點可以截斷日誌並釋放日誌空間。
• 預計需要較長的恢復。預計恢復比設定的 "Recovery Interval" 選項的值要長時。如果 "Recovery Interval" 設定為 1，意味著檢查點每分鐘一次。預設值為 0，意味著 SQL Server 將選擇合適的值，通常也是 1 分鐘。
• 請求正常關閉 SQL Server，並且不使用 NOWAIT 選項。

檢查點線程採用非順序的方式對緩衝區進行掃描。當找到一個髒頁時，會檢查該髒頁在磁碟上相鄰的頁面是否也是髒頁，以便組合進行 gather-write 大塊資料寫入，提高效能。

計畫快取（Plan Cache）

在 Buffer Pool 的緩衝區的使用組件中，除了資料緩衝（Data Cache），另一個使用量最大的就是對過程與查詢計劃的緩衝，也就是通常說的計畫快取（Plan Cache）。

SQL Server 為處資料緩衝外所有其他的緩衝機制提供通用緩衝架構，包括儲存方式和資源監視器。儲存方式包括三種：

1. Cache Store：計畫快取（Plan Cache）和行集（Rowset Clerk）為常見的 Cache Store。
2. User Store：中繼資料快取（Metadata Clerk）即為一種 User Store。
3. Object Store/Memory Pool：SNI Pooling Network Buffer 即為一種 Object Store。

Cache Store 和 User Store 採用 LRU 機制來分配和釋放空間，使用 Clock 頁面置換演算法來實現。而 Object Store 則只是大塊的記憶體，不需要 LRU 機制。

Cache Store 使用雜湊表來加快查詢速度，而 User Store 則未使用雜湊表，Object Store 也未使用雜湊表。

通過查看 sys.dm_os_memory_cache_clock_hands 視圖，尤其是 removed_last_round_count 列，如果該值在急劇增加，那麼是出現記憶體壓力的顯著徵兆。

SELECT cache_address    ,[name]    ,[type]    ,clock_hand    ,clock_status    ,removed_last_round_countFROM sys.dm_os_memory_cache_clock_hands;

Memory Broker

在 SQL Server 中有大量的組件需要使用記憶體，為了確保每個組件都在有效使用記憶體，SQL Server 使用 Memory Broker 分析與記憶體消耗相關的行為，並改善動態記憶體分配。

Memory Broker 可以在 Buffer Pool、Query Executor、Query Optimizer 等各種使用緩衝的組件間調度記憶體配置。通過監視記憶體的需求與消耗，通過帶有反饋和改進機制的動態分配演算法，協調各組件間形成最佳的記憶體配置方式。

SELECT *FROM sys.dm_os_ring_buffersWHERE ring_buffer_type = ‘RING_BUFFER_MEMORY_BROKER‘;

觀察記憶體

• sys.dm_os_memory_clerks 該視圖描述 SQL Server 執行個體中正在使用記憶體的組件職責。
• sys.dm_os_memory_objects 該視圖描述 SQL Server 當前分配的記憶體對象。
• sys.dm_os_memory_nodes 該視圖描述分配的 NUMA Node 相關資訊。
• sys.dm_os_memory_pools 該視圖顯示 Object Store 相關資訊。
• sys.dm_os_memory_cache_counters 該視圖描述 Cache Store 和 User Store 的運行情況快照。
• sys.dm_os_memory_cache_hash_tables 該視圖描述活躍的緩衝資訊。
• sys.dm_os_memory_cache_clock_hands 該視圖描述 Clock 頁面置換演算法的相關資訊。

 

本系列文章《人人都是 DBA》由 Dennis Gao 發表自部落格園個人技術部落格，未經作者本人同意禁止任何形式的轉載，任何自動或人為的爬蟲轉載或抄襲行為均為耍流氓。

人人都是 DBA（IV）SQL Server 記憶體管理