兩種管理模式
| 模式 | 運作機制 | 適用場景 | 優點 | 缺點 |
|---|
| 動態配置 (預設) | SQL Server 依負載自動向 OS 申請/釋放記憶體，範圍在 min server memory ~ max server memory 間。 | 專用資料庫伺服器、負載波動大、混合工作負載 (OLTP + 批次)。 | 零管理、自適應、最大化緩衝區命中率。 | 可能佔用 OS/其他程序所需記憶體、若 max 設定不當導致分頁。 |
| 靜態配置 | 啟動時固定配置 max server memory = min server memory，不再動態調整。 | 共用伺服器 (同機運行 IIS/其他服務)、嚴格資源配額、可預測負載。 | 保護其他程序記憶體、行為可預測。 | 無法利用閒置記憶體提升效能、配置過小導致嚴重分頁/緩衝區淘汰。 |
關鍵設定參數
-- 查看目前設定
EXEC sp_configure 'show advanced options', 1; RECONFIGURE;
EXEC sp_configure 'min server memory'; -- 預設 0 MB
EXEC sp_configure 'max server memory'; -- 預設 2147483647 MB (無上限)
-- 典型生產設定 (專用 64GB 伺服器，保留 4GB 給 OS)
EXEC sp_configure 'max server memory', 60000; -- 60 GB
RECONFIGURE;
-- 共用伺服器 (總 32GB，SQL 固定 20GB)
EXEC sp_configure 'min server memory', 20000;
EXEC sp_configure 'max server memory', 20000;
RECONFIGURE;
「保留實體記憶體」 (Lock Pages in Memory) 進階選項
- 啟用方式：授予 SQL Server 服務帳號 鎖定記憶體頁面 (Lock pages in memory) 使用者權利 → 重啟服務。
- 效果：防止 SQL Server 記憶體被分頁至磁碟，保證低延遲。
- 雙面刃：記憶體壓力時 OS 無法回收，可能導致其他關鍵服務 (網域控制器、叢集、備份代理) OOM 崩潰。僅限專用伺服器、實體記憶體 ≥ 16GB、負載可預測 時啟用。
- Enterprise Edition 才支援 (Standard Edition 需追蹤旗標 845，不建議生產使用)。
如何計算最佳 max server memory
- 基準法：總實體記憶體 - OS 保留 (4GB 或 10% 取較大) - 其他服務 (SSIS/SSRS/SSAS/備份/防毒/監控代理) 預估峰值。
- 監測法：持續收集效能計數器 (PerfMon / sys.dm_os_performance_counters)：
SQLServer:Buffer Manager \ Page life expectancy > 300 秒 (穩定)。SQLServer:Buffer Manager \ Buffer cache hit ratio > 99%。Memory \ Pages/sec 持續 < 10 (偶爾尖峰可接受)。Process(sqlservr) \ Working Set 穩定接近 max server memory。
- 若
Pages/sec 高、PLE 低 → 漸增 max server memory 直到指標改善；若 OS 可用記憶體長期 < 1GB → 降低設定。
常見誤區與對策
| 誤區 | 事實 | 對策 |
|---|
| 「SQL Server 記憶體只增不減，是記憶體洩漏」 | 設計使然：緩衝區佔用後不主動釋放，除非 OS 發出低記憶體通知 (WM_LOWMEMORY) 或達到 max 限制。 | 設定合理 max、監控 PLE/Working Set，非洩漏。 |
| 「設定 max 後 SQL 不會超過」 | max server memory 僅限制 **緩衝集區** (Buffer Pool)，不含：執行緒堆疊、CLR、擴展存儲程序、連結伺服器、備份緩衝、Columnstore 物件池 (SQL 2016+)、記憶體優化表。 | 總記憶體足印 ≈ max + 500MB~2GB (視版本/功能)，規劃 OS 保留時需加總。 |
| 「min server memory 是啟動配置量」 | 啟動時僅配置少量，隨負載增長至 min 後**不再釋放**至 min 以下。 | min 用於防止記憶體被其他程式搶佔導致 SQL 反覆申請/釋放抖動。 |
| 「AWE / Lock Pages 解決所有記憶體問題」 | 32 位元專用技術，64 位元原生支援大記憶體，AWE 已淘汰。Lock Pages 有風險。 | 64 位元環境直接設定 max，勿用 AWE/Lock Pages 除非極特殊場景。 |
疑難排解清單：記憶體壓力徵兆
-- 1. 緩衝區命中率低
SELECT cntr_value FROM sys.dm_os_performance_counters
WHERE counter_name = 'Buffer cache hit ratio' AND instance_name = '';
-- 2. 預期壽命低 (秒)
SELECT cntr_value FROM sys.dm_os_performance_counters
WHERE counter_name = 'Page life expectancy' AND instance_name = '';
-- 3. 記憶體配置 clerks 明細 (找出大戶)
SELECT type, SUM(pages_kb)/1024.0 AS MB
FROM sys.dm_os_memory_clerks
GROUP BY type ORDER BY MB DESC;
-- 4. 查詢工作區記憶體授與/等待
SELECT * FROM sys.dm_exec_query_memory_grants WHERE grant_time IS NULL; -- 等待中
-- 5. OS 層級可用記憶體
-- PerfMon: Memory \ Available MBytes (持續 < 500 MB 為警戒)
結語
**動態配置 + 合理 max server memory** 為 95% 場景最佳選擇。靜態配置、Lock Pages 僅在嚴格資源隔離、專用大記憶體伺服器、負載極度可預測時考慮。**持續監控 PLE、命中率、Pages/sec** 比死記設定值更重要。記憶體管理是 SQL Server 效能基石，值得投入時間建立基準線與告警。
