SQL Server 記憶體佔用過高的原因分析與解決方案

SQL Server 的記憶體管理機制旨在最大化效能，但若不加限制，可能導致系統記憶體耗盡，影響其他應用程式運行。本文深入剖析 SQL Server 記憶體佔用的三大核心來源，並提供實務上的配置建議。

一、記憶體開銷的三大來源

1. 資料緩存 — 最大的記憶體消費者

SQL Server 會盡可能將常用資料頁面載入記憶體（Buffer Pool），包含已讀取的資料頁與預測即將被查詢的頁面。當記憶體充足時，命中率極高，STATISTICS IO 顯示的 physical reads 往往為 0。這雖然加速查詢，但若系統上同時運行其他應用，SQL Server 雖會在記憶體壓力下釋放緩存，但頻繁的執行緒切換與 I/O 等待仍會造成效能抖動。

2. 查詢執行開銷 — Hash Join、排序與暫存物件

• Hash Join：建立雜湊表需大量記憶體，遠高於 Merge Join 或 Nested Loops Join。
• 排序操作：ORDER BY、GROUP BY、DISTINCT 若無索引支援，會在 tempdb 中進行外部排序，佔用大量記憶體。
• 中間暫存表 / 資料表變數 / 游標：這些物件在記憶體或 tempdb 中佔用空間，且游標還會維持開放的結果集。

優化方向：在常用於聯結、排序、篩選的欄位上建立適當索引，避免不必要的排序與雜湊運算，減少游標與暫存表的使用。

3. 執行計畫與系統中繼資料

編譯後的執行計畫、系統目錄資訊等佔用相對較小，通常不是記憶體瓶頸的主因。

二、資料緩存對效能的雙面影響

若伺服器專供 SQL Server 使用，緩存越大越好，甚至可考慮使用 DBCC PINTABLE（舊版本）或將關鍵資料表鎖定在記憶體中。但在混合負載環境下，必須設定 最大伺服器記憶體，避免擠佔作業系統與其他應用所需記憶體。

設定方式：

• SSMS → 伺服器屬性 → 記憶體 頁籤 → 最大伺服器記憶體 (MB)
• 或使用 sp_configure 'max server memory',

建議預留 4GB~8GB 或總記憶體的 10%~15% 給作業系統，視負載而定。

三、查詢記憶體開銷：越低越好

查詢佔用的記憶體不會像緩存那樣帶來持續效益，反而可能導致查詢排隊、記憶體授予等待。核心優化原則：

1. 為聯結鍵、排序欄位、篩選欄位建立覆蓋索引，消除 Sort 與 Hash Match 運算子。
2. 改寫複雜查詢，拆解大查詢為小批次，避免單一查詢申請過大記憶體授予。
3. 監控 sys.dm_exec_query_memory_grants 與 sys.dm_os_memory_clerks 找出記憶體大戶。

總結：合理設定最大記憶體、針對性建立索引、優化查詢計畫，三管齊下才能讓 SQL Server 在高併發環境下穩定、高效運行。