前言
「SQL Server 吃光記憶體且不釋放」是經典誤解。SQL Server 設計為**主動管理記憶體**：在不受外部壓力時盡量佔用可用物理記憶體作為緩衝池，需求時主動釋放。本文拆解三大記憶體消費組件、效能影響機制、配置策略與查詢層面優化方向。
一、 三大記憶體消費組件
- 資料緩衝池 — 最大塊： 缓存數據頁、索引頁。邏輯：用過的 + 預讀的 → 留住熱數據。記憶體充足時 **Physical Reads = 0** (統計 `STATISTICS IO`)。益處： 純記憶體讀取，極速。風險： 與其他應用搶記憶體 → 作業系統分頁/交換 → 延遲飆升。
- 查詢工作區 — 變動大戶： Hash Join (建立雜湊表)、Sort (排序暫存)、Spool (中間結果)、Cursor (游標暫存)。Hash Join 佔用最大，Merge Join / Nested Loops 較小。對策： 關聯/排序欄位加索引 → 降級為 Nested Loops / 避免 Sort。
- 執行計劃快取與系統結構 — 相對固定小額： 查詢計劃、系統目錄元數據、鎖結構等。
二、 記憶體配置策略
| 場景 | Max Server Memory 設定 | 理由 |
|---|
| 專用資料庫伺服器 (無其他應用) | **不限制** (預設 2147483647 MB) | 讓緩衝池最大化，Physical Reads 趨近 0，效能最優 |
| 共存應用伺服器 (Web/App/其他 DB) | **強制限制** = 物理記憶體 - OS保留(1-4GB) - 其他應用峰值需求 | 防止記憶體壓力觸發分頁/交換，雖 SQL 會主動釋放，但釋放過程 (Lazy Writer、Checkpoint、鎖釋放) 有延遲 |
設定途徑：SSMS → 伺服器屬性 → 記憶體頁籤 → 「最大伺服器記憶體」 / `sp_configure 'max server memory', N; RECONFIGURE;`。
三、 查詢層面優化：降低工作區記憶體申請
- 避免中間表/暫存表濫用： 改用 CTE、衍生表、APPLY、窗口函數推導邏輯。
- 避免游標： 改用集合操作。
- 關聯/排序欄位建索引： 將 Hash Join/Sort 降級為 Nested Loops / 索引有序掃描，大幅降低記憶體授予。
- 統計資料維護： 過期統計導致基數估算偏差 → 記憶體授予過大 (浪費) 或過小 (溢寫 Tempdb)。
- Resource Governor (企業版)： 限制特定工作負載組的記憶體授予上限 (REQUEST_MAX_MEMORY_GRANT_PERCENT)。
四、 監控關鍵指標
- `DBCC MEMORYSTATUS` / `sys.dm_os_process_memory` / `sys.dm_os_memory_clerks`：詳細 clerks 佔用分佈。
- `Performance Counter: SQL Server:Buffer Manager \ Target Pages / Database Pages / Stolen Pages`。
- `SQL Server:Memory Manager \ Memory Grants Pending / Outstanding`：查詢等待記憶體授予狀況。
五、 總結
SQL Server 高記憶體佔用**非 Bug 而是特性**。專用機**不限制**讓緩衝池發揮最大效能；共存機**硬性限制**避免系統級資源爭用。查詢端核心在於**索引設計消除 Hash Join/Sort**，從源頭降低工作區記憶體壓力。監控 `Memory Grants Pending` 非零即為記憶體壓力訊號，需優化查詢或擴容。
