解決 SQL Server 佔用記憶體過高問題：原理分析與優化策略

現象描述

常見問題：MSSQL 記憶體佔用極高且持續增長，或已設定最大記憶體卻未生效。這究竟是怎麼回事？

SQL Server 記憶體使用機制

1. 資料快取——最大開銷

若記憶體充足，SQL Server 會將用過的資料與預測會用到的資料全部載入記憶體，直到記憶體不足時才淘汰命中率低的資料。這就是為什麼 STATISTICS IO 中常看到 physical reads = 0 的原因。

2. 查詢執行開銷

• Hash Join：記憶體開銷較大
• Merge Join / Nested Loop：開銷較小
• 排序、中繼表、游標：亦會佔用大量記憶體

建議：常用於關聯與排序的欄位應建立索引。

3. 執行計畫與系統資料儲存

佔用相對較小，通常不是瓶頸。

優化策略

情境一：無其他應用程式爭用記憶體

資料快取越多越好，甚至可手動將熱門資料 Pin 在快取 中。不建議限制 SQL Server 記憶體上限。

情境二：共存其他應用程式

雖然 SQL Server 在需要時會釋放記憶體，但執行緒切換、I/O 等待會造成效能下降。必須設定最大記憶體上限：

• 企業管理器 → SQL Server 屬性 → 記憶體頁籤
• 或使用 sp_configure 設定

查詢層面優化

查詢記憶體開銷越低越好。避免使用中繼表與游標；在頻繁 JOIN 與排序的欄位建立索引。

總結

• 理解記憶體分配機制是優化前提
• 獨佔伺服器：放開記憶體限制
• 共享伺服器：設定合理上限
• 從查詢計畫著手：索引、避免游標、優化 JOIN