SQL Server 記憶體管理是動態、自我調節的。理解其三大核心消費者——**資料緩衝區**、**查詢工作區 (工作空間記憶)**、**計畫快取/系統結構**——的運作邏輯與配置策略,是解決「記憶體佔用過高/不釋放/未用滿」疑難雜症的鑰匙。
一、 記憶體三大消費者拆解
1. 資料緩衝區 —— 最大塊頭
- 功能:缓存資料頁、索引頁 (8KB 頁),實現邏輯讀替代物理讀。
- 策略:**盡可能佔用可用記憶體** (受 `Max Server Memory` 上限約束)。
- 淘汰算法:近似 **LRU-K (K=2)**,配合 **參考計數**、**脏頁清理 (Checkpoint/Lazy Writer)**。
- 指標:`Page Life Expectancy (PLE)` > 300 秒為健康;`Buffer Cache Hit Ratio` > 99%。
- 無其他應用爭搶時:越大越好,甚至可用 `DBCC PINTABLE` (已棄用/慎用) 釘住熱點表。
- 共存其他應用時:**必須設定 `Max Server Memory`** 留足 OS (4-8GB)、其他程序、驅動程式、備份緩衝、記憶體映射檔空間。未設上限會導致 OS 記憶體不足觸發分頁/交換,引發災難性效能崩潰。
2. 查詢工作區記憶 —— 隱形殺手
- 用途:Hash Join / Hash Match 聚合、Sort (排序)、Parallelism 交換緩衝、游標、Table Spool、索引建立/重建排序。
- 授予機制:**Memory Grant (記憶體授予)** —— 優化器根據基數估算預估所需記憶體,向 Resource Semaphore 申請。
- 高消耗操作:
- Hash Join / Hash Aggregate:建立雜湊表需大量記憶體。
- Sort:大結果集排序需大量記憶體 (外部排序溢寫 TempDB)。
- Parallelism:交換緩衝區 (Exchange Buffers) 消耗記憶體。
- 低消耗操作:Nested Loops Join、Merge Join (需預序輸入)、Stream Aggregate。
- 優化方向:
- **關聯/排序欄位建立索引** → 避免 Hash/Sort,改用 Merge/Nested Loops/Stream Aggregate。
- 避免 `SELECT *`、避免不必要的 `ORDER BY`、`DISTINCT`、`GROUP BY`。
- 避免大結果集游標、臨時表、表變數濫用。
- 更新統計資訊 → 準確基數估算 → 避免嚴重低估導致記憶體授予不足溢寫 TempDB、或高估佔用記憶體阻塞其他查詢。
3. 計畫快取與系統結構 —— 基礎開銷
- 執行計畫快取、物件中繼資料、鎖結構、交易上下文、連接上下文。
- 通常佔用較小 (數百 MB 級),但**計畫快取膨脹** (大量 Ad-hoc 查詢、未參數化) 可達數 GB。
- 對策:啟用 `Optimize for Ad hoc Workloads`、強制參數化、定期 `DBCC FREEPROCCACHE` (謹慎)、Plan Guide/Query Store 固定優良計畫。
二、 關鍵配置:Max Server Memory (最大伺服器記憶體)
| 場景 | 建議設定 | 理由 |
| 專用資料庫伺服器 (無其他應用) | **不設限 (預設 2147483647 MB)** 或 總物理記憶體 - 4GB~8GB (留給 OS) | 最大化緩衝區命中率,SQL Server 會主動釋放記憶體回應 OS 低記憶體通知 |
| 共存應用伺服器 (IIS、App Server、報表服務等) | **嚴格設定** = 總物理記憶體 - OS預留(4-8GB) - 其他應用峰值佔用 - 緩衝(2-4GB) | 防止 OS 記憶體耗盡觸發分頁/交換,導致 SQL Server 線程阻塞、查詢超時、甚至服務崩潰 |
| 虛擬機/容器環境 | 設定為 **VM/Container 記憶體限制 - 20%~30%** | Hypervisor/Container Runtime 亦需記憶體開銷;避免 Ballooning/ Swapping |
設定方式:SSMS → 伺服器屬性 → 記憶體 / `sp_configure 'max server memory (MB)', N; RECONFIGURE;` (無需重啟)。
三、 常見症狀診斷與對策
| 症狀 | 可能原因 | 排查 DMV / 工具 | 對策 |
| 記憶體持續增長不釋放 | 正常行為 (緩衝區成長) / Max Memory 未設 / 記憶體洩漏 (罕見) | `DBCC MEMORYSTATUS`、`sys.dm_os_process_memory`、`sys.dm_os_memory_clerks` (type='MEMORYCLERK_SQLBUFFERPOOL') | 設定 Max Memory;若真洩漏開 Case 給微軟 |
| 設定 Max Memory 但實際用量遠低 | 負載輕、資料量小、啟動後未預熱、AWE/大頁面配置問題 | `sys.dm_os_buffer_descriptors` 計數、`DBCC MEMORYSTATUS` 目標/提交/目前 | 屬正常;預熱腳本預載熱點資料;檢查 Lock Pages in Memory 權限 |
| 查詢慢、等待 RESOURCE_SEMAPHORE | 查詢工作區記憶體不足,排隊等待 Grant | `sys.dm_exec_query_memory_grants`、`sys.dm_exec_requests` (wait_type=RESOURCE_SEMAPHORE) | 優化查詢 (索引/統計/改寫);調高 Max Memory;限制平行度 MAXDOP |
| Plan Cache 佔用過大 | 大量 Ad-hoc/單次計畫 | `sys.dm_exec_cached_plans` (objtype='Adhoc', usecounts=1) 佔比 | `sp_configure 'optimize for ad hoc workloads', 1`;參數化應用程式 SQL |
四、 監控儀表板核心指標
- 目標/總計/目前伺服器記憶體 (KB):`DBCC MEMORYSTATUS` 或 `sys.dm_os_process_memory` / Performance Counter `SQLServer:Memory Manager\Target/Total/Database Cache Memory (KB)`。
- PLE (Page Life Expectancy):`sys.dm_os_performance_counters` counter_name='Page life expectancy'。持續 < 300 秒警示。
- Memory Grants Pending / Outstanding:`sys.dm_exec_query_memory_grants` / Counter `Memory Grants Pending`。> 0 表示查詢在排隊等記憶體。
- Buffer Cache Hit Ratio:> 99% 為佳。
- Stolen Memory / Reserved Memory** 比例:`MEMORYCLERK_SQLQUERYEXEC` (工作區)、`MEMORYCLERK_SQLPLAN` (計畫)、`CACHESTORE_SQLCP`/`OBJCP`。
結語
SQL Server 記憶體管理的核心哲學是 **「動態平衡、緩衝優先、授予可控」**。DBA 的核心職責是:**設定合理的 `Max Server Memory` 邊界**、**持續監控 PLE/Grants/Plan Cache**、**針對高消耗查詢進行索引/統計/改寫優化**,而非試圖微管理每一 MB。記住:**記憶體是為了用來加速查詢的,閒置才是浪費;但邊界失控會拖垮整台機器。**