SQL Server 記憶體管理機制與最佳化設定指南

SQL Server 如何使用記憶體？

SQL Server 的記憶體消耗主要集中在三大區塊：

1. 資料快取（最大開銷）：將熱門資料頁面載入記憶體，若記憶體充足會盡量保留，直到系統記憶體壓力觸發淘汰低命中率頁面。這就是為何 STATISTICS IO 常顯示 physical reads = 0。
2. 查詢執行開銷：Hash Join、排序、中繼表、游標等操作需額外工作記憶體。適當索引可減少 Hash Join 與排序，降低記憶體壓力。
3. 執行計畫與系統中繼資料：佔用較小，通常不是瓶頸。

資料快取對效能的影響

若伺服器專供 SQL Server 使用，快取越大越好，甚至可手動 PIN 關鍵資料表。但若共存其他應用程式，SQL Server 雖會動態釋放記憶體，但執行緒切換、I/O 等待仍會造成效能抖動。此時必須設定 最大伺服器記憶體，透過企業管理器「屬性 → 記憶體」頁籤或 sp_configure 'max server memory' 限制上限。

查詢記憶體開銷的優化方向

• 避免不必要的中繼表與游標，改用集合式操作。
• 在頻繁 JOIN、排序、分組的欄位建立索引，引導優化器選擇 Nested Loops 或 Merge Join，減少 Hash Join 記憶體申請。
• 監控 Memory Grants Pending、Memory Grants Outstanding 等計數器，發現持續等待則需調整查詢或增加記憶體。

實務建議

專用資料庫伺服器：不限制 記憶體使用，讓 SQL Server 自動管理。
共用伺服器：設定 max server memory 為實體記憶體的 70%-80%，預留給 OS 與其他程式；min server memory 可設為固定值避免釋放過多導致後續分配延遲。