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

2. 壓力來源判斷：外部 vs 內部

3. 解決策略

外部壓力

• 專機運行 SQL Server，避免共置其他服務。
• 將大量記憶體操作（備份、索引重建）排程至離峰時段（如凌晨 1-2 點）。

內部壓力

1. 找出高 I/O 查詢並調優：

   -- 物理讀取最多的 100 條
   SELECT TOP 100 qs.total_physical_reads, qs.execution_count,
          qs.total_physical_reads / qs.execution_count AS avg_io,
          qt.text, DB_NAME(qt.dbid) AS dbname, qt.objectid
   FROM sys.dm_exec_query_stats qs
   CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
   ORDER BY qs.total_physical_reads DESC;
   
   -- 邏輯讀取最多的 100 條
   SELECT TOP 100 qs.total_logical_reads, qs.execution_count,
          qs.total_logical_reads / qs.execution_count AS avg_io,
          qt.text, DB_NAME(qt.dbid) AS dbname
   FROM sys.dm_exec_query_stats qs
   CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
   ORDER BY qs.total_logical_reads DESC;

2. 若查詢已無調優空間 → 增加實體記憶體 是最直接有效的手段。

二、Buffer Pool 中 Stolen Memory 壓力調優

1. 透過 Memory Clerk 分析

Stolen 記憶體皆由 SQL Server 內部組件（Clerk）申請，查詢 sys.dm_os_memory_clerks 即可定位大戶：

2. 透過 wait_type 定位

• CMEMTHREAD (0x00B9)：高併發下多執行緒爭奪同一快取區記憶體 → 改用預存程序/參數化查詢提高計畫重用率。
• RESOURCE_SEMAPHORE_QUERY_COMPILE (0x011A)：過於複雜的查詢編譯需求大量記憶體，觸發編譯記憶體上限等待 → 簡化查詢、拆分預存程序、定期 DBCC FREEPROCCACHE 或升級 64 位元版本。

三、Multi-Page Memory (MemToLeave) 壓力調優

32 位元 SQL Server 預設僅 384 MB，易成瓶頸。三大常見成因：

1. Network Packet Size 設過大（> 8 KB）且連接數多 → 改回預設 4 KB 或升級 64 位元。
2. 超大執行計畫 (> 8 KB) 佔用 Multi-Page 空間，且不觸發 Lazy Writer 清理 → 調優查詢縮小計畫、定期 DBCC FREEPROCCACHE、升級 64 位元。
3. CLR / 擴充預存程序 直接在 MemToLeave 分配大塊記憶體 → 檢查第三方元件記憶體使用模式。

對應 wait_type = SOS_RESERVEDMEMBLOCKLIST 即為等待 Multi-Page 記憶體。

本文整理自實戰筆記，如有遺漏歡迎補充。上篇請見《SQL Server 效能調優：記憶體管理篇（上）》。