SQL Server 記憶體管理機制與壓力解決方案：從緩衝區到查詢工作區的完整調優指南

「SQL Server 吃光所有記憶體」是經典誤解。本文揭示 SQL Server 動態記憶體管理架構、三大消費者 (緩衝區、查詢工作區、其他)、Max Server Memory 設定原則、記憶體壓力診斷查詢與解決策略。

記憶體消費三大項

動態管理機制

• 啟動時佔用 Min Server Memory (預設 0 MB，不立即佔用)
• 隨負載向 OS 申請記憶體，至 Max Server Memory (預設 2147483647 MB = 無上限)
• OS 發出低記憶體通知 (WM_MEMORY_LOW) → SQL Server 釋放緩衝區乾淨頁、縮減計畫快取
• 不會主動釋放至 Min Server Memory 以下，除非 OS 強制修剪 (極少見)

Max Server Memory 設定鐵律

-- 留給 OS 與非 SQL 進程的記憶體保留量 (建議值)
-- 總記憶體 <= 16 GB: 保留 4 GB
-- 16-32 GB: 保留 6-8 GB
-- 32-64 GB: 保留 8-12 GB
-- 64-128 GB: 保留 12-16 GB
-- >128 GB: 保留 16-24 GB
-- 公式：Max = 總實體記憶體 - 保留量

記憶體壓力診斷查詢

-- 1. 緩衝區命中率 (目標 > 99%)
SELECT 
    (1 - (physical_reads * 1.0 / (physical_reads + logical_reads))) * 100 AS BufferHitRatio
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Buffer cache hit ratio';

-- 2. 頁面壽命期望 (PLE, 目標 > 300 秒，越高越好)
SELECT [Page life expectancy] 
FROM sys.dm_os_performance_counters 
WHERE counter_name = 'Page life expectancy';

-- 3. 記憶體授予等待 (RESOURCE_SEMAPHORE)
SELECT wait_type, waiting_tasks_count, wait_time_ms, max_wait_time_ms
FROM sys.dm_os_wait_stats
WHERE wait_type IN ('RESOURCE_SEMAPHORE', 'RESOURCE_SEMAPHORE_QUERY_COMPILE');

-- 4. 查詢工作區記憶體使用詳情
SELECT 
    requested_memory_kb / 1024.0 AS Requested_MB,
    granted_memory_kb / 1024.0 AS Granted_MB,
    required_memory_kb / 1024.0 AS Required_MB,
    max_used_memory_kb / 1024.0 AS Max_Used_MB,
    query_cost, timeout_sec, resource_semaphore_id
FROM sys.dm_exec_query_memory_grants
WHERE granted_memory_kb > 0;

-- 5. 計畫快取記憶體佔用
SELECT 
    objtype, 
    COUNT(*) AS Plan_Count,
    SUM(size_in_bytes) / 1024.0 / 1024.0 AS Total_MB
FROM sys.dm_exec_cached_plans
GROUP BY objtype
ORDER BY Total_MB DESC;

常見壓力場景與解決策略

場景 A：PLE 持續偏低 (< 60 秒)、緩衝區命中率 < 95%

• 原因：記憶體不足導致頁面快速淘汰、或大量掃描查詢沖刷熱頁
• 對策：調高 Max Memory、新增實體記憶體、優化掃描查詢加索引、啟用 READ_COMMITTED_SNAPSHOT 減少鎖爭用

場景 B：RESOURCE_SEMAPHORE 等待頻繁

• 原因：大量並發排序/Hash 查詢申請記憶體超過可用授予上限 (約 Max Memory * 75%)
• 對策：建立適當索引消除排序/Hash、降低 MAXDOP、使用 Resource Governor 限制工作負載組授予上限、分批處理大量資料

場景 C：計畫快取佔用過大 (> Max Memory 20%)

• 原因：大量 Ad-hoc 查詢、未參數化、單用計畫堆積
• 對策：啟用 Optimize for Ad hoc Workloads、強制參數化 (謹慎)、定期 DBCC FREESYSTEMCACHE('SQL Plans')、改用預存程序/參數化查詢

場景 D：OS 記憶體不足、分頁檔狂增

• 原因：Max Memory 設定過高或未設定、啟用 LPIM 但 Max 未限制、記憶體洩漏 (CLR、擴展事件、驅動程式)
• 對策：立即降低 Max Memory、檢查非 SQL 進程佔用、排查 CLR/擴展事件、重啟 SQL 服務釋放

進階：Resource Governor 記憶體隔離

-- 建立資源池限制報表查詢記憶體上限 30%
CREATE RESOURCE POOL ReportPool WITH (MAX_MEMORY_PERCENT = 30);
CREATE WORKLOAD GROUP ReportGroup USING ReportPool;
-- 分類器函數導向報表登入/應用名

記憶體管理的核心哲學：給緩衝區足夠空間服務熱資料，給查詢工作區合理授予完成運算，給 OS 足夠緩衝維持系統穩定。三者平衡，系統長久穩健。

1. 上一篇文章： MySQL 索引與查詢順序奧秘：為什麼 SELECT * 不按主鍵序？
2. 下一篇文章： SQL Server 字串大小寫敏感比較：二進位轉換與排序規則兩大實戰方案

關於SqlServer

SQL Server 2014 原生備份加密：保護備份檔案安全的新標準

背景：備份外洩風險 歷次資料外洩多源於備份檔案遺失/被盜。S

SQL Server 2005 部署 FSA 檔案監控方案——資料庫建立與基礎設定

記錄 SQL Server 2005 環境下部署 FSA (

SQL Server 2012 AlwaysOn 網路抖動導致提交延遲：專用心跳網卡隔離解決方案

生產環境 SQL Server 2012 AlwaysOn

循序漸進學習 PowerBuilder 6.0（2-7）：應用程式執行實戰演示 — 登入、資料瀏覽、CRUD 操作

一、執行前準備：儲存與編譯 File → Save All