SQL Server 記憶體配置選項深度優化：min/max server memory、max worker threads、索引建立與查詢記憶體

動態記憶體管理機制

SQL Server 2000+ 採用動態記憶體管理：啟動時根據作業系統可用記憶體自動計算目標記憶體 (Target Server Memory)，運行中持續平衡 Buffer Pool、Plan Cache、Workspace Memory、Lock Memory 等消費者。原則上無需手動干預，但在特定場景下調整配置選項可獲得更穩定效能。

核心配置選項詳細解析

1. min server memory (MB)

• 功能：SQL Server 不會將 Buffer Pool 釋放低於此值 (非啟動時立即分配，而是達到後不再釋放)
• 預設：0 (無下限)
• 建議設定：
  • 單一實例專用伺服器：實體記憶體 × 0.5 ~ 0.7 (如 64GB → 32~44GB)
  • 多實例共存：依權重分配，確保各實例最低保障
  • 共存其他應用 (Web/郵件/監控)：**保留 OS 4-8GB + 其他應用需求**
• 風險：設定過高導致作業系統記憶體壓力、分頁、甚至 OOM Killer 終止 sqlservr

2. max server memory (MB)

• 功能：Buffer Pool 最大上限，防止 SQL Server 佔盡記憶體導致 OS/其他服務飢餓
• 預設：2147483647 MB (理論無上限，實際受限於實體記憶體)
• 建議設定 (留給 OS 與非 Buffer Pool 分配器)：
  Max Server Memory = 總實體記憶體 - OS 保留 - 非 BP 分配 (Columnstore/In-Memory OLTP/CLR/Thread Stack/Linked Server/Backup Buffers) - 安全裕度

• 嚴禁：min server memory = max server memory (固定記憶體，喪失動態平衡能力)
• 啟用 Lock Pages in Memory (LPIM) 權限給 SQL Server 服務帳戶，防止 Buffer Pool 被分頁到磁碟 (需企業版/標準版 2016+)

3. max worker threads

• 功能：控制可用於處理請求的工作執行緒數量 (含系統/使用者)
• 預設：0 (自動計算) → 32-bit: 256、64-bit: 512 + (邏輯 CPU - 4) × 16 (最多 32768)
• 調整場景：
  • 大量等待型負載 (阻塞、鎖等待、外部連結查詢) → 執行緒耗盡 (THREADPOOL 等待) → 增加至 1024/2048
  • 小記憶體環境 (4-8GB) → 減少至 256/384 降低執行緒堆疊記憶體佔用 (每執行緒 ~2MB 使用者模式 + 1MB 核心模式)
• 監控指標：sys.dm_os_schedulers current_workers_count、work_queue_count、THREADPOOL 等待統計

4. index create memory (KB)

• 功能：建立/重建索引時排序運算的最大記憶體授予
• 預設：0 (自動，由 Query Memory Grant 機制管理)
• 建議：通常保持 0。僅在離峰大量重建索引、且有充足記憶體時，可暫時調大 (如 500MB-2GB) 加速排序，完成後立即改回 0
• min memory per query 協同：保持預設 1024 KB，避免索引建立因等待記憶體而長時間排隊

5. min memory per query (KB)

• 功能：單一查詢執行保證的最小記憶體授予 (排序/雜湊/交換)
• 預設：1024 KB (1 MB)
• 調整：
  • 大量複雜查詢 (大排序/雜湊) 並發高 → 適度調大 (2-4 MB) 提升效能
  • 繁忙 OLTP 系統 → 勿調大，否則導致記憶體授予排隊 (RESOURCE_SEMAPHORE 等待)、並發度下降
• 查詢若需更多記憶體且可用，仍可動態取得 (受 max server memory、Resource Governor 限制)

Resource Governor：企業級記憶體隔離 (Enterprise Edition)

-- 建立資源池：限制報表工作負載最多佔用 30% 記憶體
CREATE RESOURCE POOL ReportPool WITH (MAX_MEMORY_PERCENT = 30);
CREATE WORKLOAD GROUP ReportGroup USING ReportPool;
-- 分類器函數依 APP_NAME/USER/DB 導向工作負載群組

適用於多租戶、混合工作負載 (OLTP + OLAP + ETL) 環境，硬性保障核心業務記憶體。

監控與診斷儀表板查詢

-- 記憶體配置總覽
SELECT 
    (physical_memory_in_use_kb/1024) AS sql_mem_mb,
    (locked_page_allocations_kb/1024) AS locked_mb,
    (virtual_address_space_committed_kb/1024) AS vmem_mb,
    (large_page_allocations_kb/1024) AS large_mb
FROM sys.dm_os_process_memory;

-- Buffer Pool 使用細目
SELECT 
    type, 
    SUM(pages_kb)/1024 AS mb
FROM sys.dm_os_memory_clerks
GROUP BY type
ORDER BY mb DESC;

-- 記憶體授予等待
SELECT * FROM sys.dm_exec_query_memory_grants WHERE grant_time IS NULL;

-- THREADPOOL 等待
SELECT wait_type, waiting_tasks_count, wait_time_ms, max_wait_time_ms
FROM sys.dm_os_wait_stats
WHERE wait_type = 'THREADPOOL';

最佳實務總結

1. 預設動態管理覆蓋 90%+ 場景，**優先優化查詢/索引/統計資料**
2. 設定 max server memory 為「實體記憶體 - OS/非BP 預留」
3. 啟用 LPIM (Lock Pages in Memory) 防止分頁
4. 僅在明確觀測到 THREADPOOL 等待或記憶體壓力時調整 max worker threads/min memory per query
5. 企業版部署 Resource Governor 實現工作負載級記憶體隔離
6. 建立基準線監控：Buffer Pool 命中率 > 99%、Page Life Expectancy > 300 秒、Memory Grants Pending = 0