解決 SQL Server 記憶體佔用過高：原理分析與配置優化

SQL Server 「吃光記憶體」是 DBA 最常遇到的誤解之一。實則這是其設計機制：主動緩存熱點資料以極致提升讀取效能。本文深入剖析記憶體分配機制，提供科學配置與查詢優化雙軌解決方案。

記憶體三大核心消費場景

1. 資料緩存——最大佔用者（設計使然）

SQL Server 將讀取的資料頁面、預讀預測頁面全部保留在緩衝池，直到記憶體壓力觸發 LRU 淘汰機制。這就是為何 STATISTICS IO 常顯示 physical reads = 0——資料早已在記憶體中。這不是洩漏，是特性。

2. 查詢執行記憶體——效能殺手

• Hash Join / Hash Aggregate：需建立雜湊表，大量記憶體授予。
• Sort (排序)：ORDER BY、DISTINCT、窗口函數可能溢寫到 TempDB。
• Parallelism (並行)：多執行緒交換資料需額外記憶體緩衝區。
• Cursor / Table Variable / Table Spool：中間結果集物化存儲。

對策：針對連接/排序欄位建立覆蓋索引，將 Hash Join 轉為 Nested Loops / Merge Join；避免不必要的排序與游標；優化查詢計畫減少記憶體授予請求。

3. 計畫快取與系統中繼資料——相對固定開銷

編譯後的執行計畫、系統目錄緩存、鎖結構等，通常佔用較小且穩定。

科學配置最大記憶體

場景 A：專用資料庫伺服器（無其他應用）

不限制最大記憶體，讓 SQL Server 全權管理。作業系統保留 1-4 GB 即足夠（鎖定頁面記憶體權限 Lock Pages in Memory 可防分頁）。

場景 B：共置應用伺服器（含 IIS、中介軟體等）

必須設定 Max Server Memory，預留作業系統與其他進程所需記憶體：

• 企業管理器 → 伺服器屬性 → 記憶體頁籤 → 最大伺服器記憶體 (MB)
• 或 T-SQL：EXEC sp_configure 'max server memory (MB)', 4096; RECONFIGURE;

經驗值：總物理記憶體 - (OS 2-4GB + 其他進程峰值) = SQL Server Max Memory。

查詢層面優化：治本之策

單純限制記憶體會導致緩存命中率下降、物理 I/O 激增、查詢變慢。真正解決之道在於：

1. 識別高記憶體授予查詢：sys.dm_exec_query_memory_grants / Query Store。
2. 補齊缺失索引，消除 Hash Match、Sort、Spool 操作符。
3. 重寫游標/迴圈為集合式操作，拆分大交易減少鎖記憶體。
4. 啟用 OPTIMIZE FOR AD HOC WORKLOADS 減少單用計畫快取膨脹。

監控儀表盤

關鍵效能計數器：

• SQLServer:Buffer Manager \ Page Life Expectancy > 300 秒為健康
• SQLServer:Memory Manager \ Target Server Memory / Total Server Memory 收斂穩定
• SQLServer:Memory Manager \ Memory Grants Pending 持續為 0