SQL Server 效能調優實戰：記憶體管理篇（上）

前言：記憶體是 SQL Server 的生命線

對 SQL Server 而言，記憶體、磁碟、CPU 三大資源中，記憶體優先級最高。SQL Server 會盡可能將熱點資料頁緩存在記憶體中以降低 I/O。本文系統梳理 SQL Server 記憶體管理體系，分為作業系統層與 SQLOS 內部兩層次。

一、作業系統層面的記憶體管理

1. Windows 關鍵記憶體術語

2. Windows 記憶體監控計數器

工具：效能監視器。參考《使用「效能監視器」監視系統效能/運行狀況》。

二、SQL Server 內部記憶體管理

1. 記憶體用途分類

2. 依申請方式分類

• Reserve → Commit：資料頁（Database Page）。
• Stolen (直接 Commit)：除資料頁外幾乎所有 Consumer、執行計畫、連接資訊等。

3. 依申請單位大小分類

• Buffer Pool：≤ 8 KB 單位（單頁）。
• Multi-Page (MemToLeave)：> 8 KB 單位（大物件、執行緒堆疊、CLR 大物件等）。

4. 32 位元 vs 64 位元 記憶體上限差異

5. 記憶體使用情況分析兩大工具

效能計數器

動態管理視圖 (DMV) - Memory Clerk 視角

SQL Server 2005+ 統一由 Memory Clerk 管理所有記憶體申請/釋放。查詢腳本：

SELECT type,
       SUM(virtual_memory_reserved_kb)   AS vm_Reserved_kb,
       SUM(virtual_memory_committed_kb)  AS vm_Committed_kb,
       SUM(awe_allocated_kb)             AS awe_Allocated_kb,
       SUM(shared_memory_reserved_kb)    AS sm_Reserved_kb,
       SUM(shared_memory_committed_kb)   AS sm_Committed_kb,
       SUM(single_pages_kb)              AS SinglePage_kb,   -- Buffer Pool 中 Stolen
       SUM(multi_pages_kb)               AS MultiPage_kb     -- MemToLeave
FROM sys.dm_os_memory_clerks
GROUP BY type
ORDER BY type;

衍生指標：

• Reserve/Commit = vm_Reserved / vm_Committed
• Stolen = SinglePage + MultiPage
• Buffer Pool = vm_Committed + SinglePage
• MemToLeave = MultiPage

進階：查看緩衝池中具體缓存了哪些表、哪些索引

-- 跨資料庫掃描緩衝描述符
DECLARE @name NVARCHAR(100), @cmd NVARCHAR(1000);
DECLARE dbnames CURSOR FOR SELECT name FROM master.dbo.sysdatabases;
OPEN dbnames;
FETCH NEXT FROM dbnames INTO @name;
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @cmd = 'SELECT b.database_id, DB_NAME(b.database_id) AS db, p.object_id, p.index_id, COUNT(*) AS buffer_count '
             + 'FROM ' + @name + '.sys.allocation_units a, '
             + @name + '.sys.dm_os_buffer_descriptors b, '
             + @name + '.sys.partitions p '
             + 'WHERE a.allocation_unit_id = b.allocation_unit_id '
             + 'AND a.container_id = p.hobt_id '
             + 'AND b.database_id = DB_ID(''' + @name + ''') '
             + 'GROUP BY b.database_id, p.object_id, p.index_id '
             + 'ORDER BY b.database_id, buffer_count DESC';
    EXEC (@cmd);
    FETCH NEXT FROM dbnames INTO @name;
END
CLOSE dbnames; DEALLOCATE dbnames;

-- 依 object_id 查表名
SELECT s.name AS table_schema, o.name AS table_name
FROM sys.objects o JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE o.object_id = @object_id;

-- 依 index_id 查索引名
SELECT name AS index_name FROM sys.indexes WHERE object_id = @object_id AND index_id = @index_id;

查看快取的執行計畫與對應 SQL 文本

SELECT usecounts, refcounts, size_in_bytes, cacheobjtype, objtype, text
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
ORDER BY objtype DESC;

本文側重資料蒐集與架構認知，具體調優手法將在下篇（資料頁、Stolen、Multi-Page 三大壓力專題）詳細說明。