深入解析 SQL Server 執行語句時的記憶體消耗與 I/O 行為

SQL Server 執行 SQL 語句的性能評估向來以 I/O 讀取數為核心指標。本文在遵循此原則的前提下，透過實際測試深入分析不同 SQL 語句執行時 SQL Server 記憶體的變化情況，結合執行計畫與 I/O 統計，為你揭示索引策略與聯接方式對記憶體佔用的實際影響。

SQL Server 記憶體佔用特點速覽

除核心引擎進程外，SQL Server 主要佔用兩類記憶體：資料緩衝區和執行計畫快取。資料以 8KB 頁為單位存儲，與磁碟頁大小一致。查詢執行時，若所需資料已在記憶體中，直接從緩衝區讀取（邏輯讀）；否則先從磁碟載入緩衝區（物理讀）。我們常關注的邏輯讀次數即對應從緩衝區讀取的頁數，物理讀則對應從磁碟讀取的頁數。

測試環境與準備

為精確觀察單條語句引起的記憶體變化，測試在無並發的獨立資料庫進行。每次測試前執行 CHECKPOINT; DBCC DROPCLEANBUFFERS 清空資料緩衝區（生產環境慎用）。並開啟 SET STATISTICS IO ON 及實際執行計畫顯示（Ctrl+M）。

測試 1：無索引堆表上的查詢行為

1.1 全表掃描 / 低選擇性查詢

SELECT * FROM P_User

執行計畫顯示 Table Scan。I/O 統計：邏輯讀 4、物理讀 4。查詢 sys.dm_os_buffer_descriptors 發現記憶體中除必要的 PFS、IAM 管理頁外，恰好出現 4 個 Data Page，與統計一致，驗證表佔用 4 個資料頁、共 1000 行。

1.2 高選擇性查詢

SELECT TOP 1 * FROM P_User WHERE MobileNo = 28502

無索引時仍需掃描全表，所有資料頁均讀入記憶體。若僅 SELECT TOP 1 * FROM P_User，可能僅讀取部分頁，但因堆表無序，結果不穩定。

測試 2：建立叢集索引後的變化

2.1 全表掃描

在 MobileNo 上建立叢集索引後，同樣查詢執行計畫變為 Clustered Index Scan。I/O 統計：邏輯讀 6、物理讀 1、預讀 4。邏輯讀增加 2 次，源於叢集索引根頁與中間頁的遍歷。記憶體中新增一個非葉級索引頁，葉級索引頁與資料頁共存。

透過 sys.dm_db_index_physical_stats 確認索引共 2 級。物理讀僅 1 次（根頁），其餘資料頁透過預讀載入，實際執行成本更低。

2.2 高選擇性查詢

有叢集索引時，SELECT * FROM P_User WHERE MobileNo = 28702 僅將目標記錄所在頁讀入記憶體，極大減少 I/O 與記憶體佔用。

測試 3：非叢集索引場景

3.1 全表掃描

將叢集索引改為非叢集索引，全表掃描行為與無索引相似，查詢優化器無法利用非叢集索引，仍讀取所有資料頁。

3.2 高選擇性查詢

執行計畫顯示 Index Seek + Key Lookup（覆蓋索引除外），僅讀取目標資料所在頁。

測試 4：巢狀迴圈聯接

準備 P_Order 表（10000 行，38 頁）。強制 INNER LOOP JOIN：

• 雙表無索引：邏輯讀超 6 萬次，雙表所有頁載入記憶體。
• 單表有叢集索引：邏輯讀降至 2 萬左右，聯接順序影響大（前表為外部輸入）。
• 雙表均有叢集索引：邏輯讀降至數千次，僅輸出所需資料頁載入記憶體。

高選擇性聯接（帶 WHERE）下，有索引表執行 Index Seek，無索引表全掃描；雙表有索引時邏輯讀極低（2-3 次）。

測試 5：合併聯接

• 無索引 / 單表有索引：雙表全掃描，使用臨時表，邏輯讀 ≈ 兩表頁數之和。
• 雙表有叢集索引：仍讀取所有資料頁，但邏輯讀大幅減少（一表總頁數 + 輸出頁數），無需臨時表。
• 高選擇性下 Merge Join 無法生成計畫，需改用低選擇性範圍查詢，但 Nested Loop 更優。

測試 6：雜湊聯接

• 無論是否有索引，Hash Join 均讀取所有資料頁至記憶體，使用臨時表，邏輯讀 ≈ 兩表頁數之和。
• 高選擇性同樣無法直接執行，低選擇性範圍查詢下仍不如 Nested Loop。

核心結論與實戰建議

1. 單表查詢：高選擇性建非叢集索引（推薦）或叢集索引；低選擇性需叢集索引。
2. 聯接查詢：結果集小用 Nested Loop Join；結果集大，建叢集索引走 Merge Join；避免 Hash Join，轉換為前兩者提升性能。

本測試結合記憶體變化、執行計畫與 I/O 統計，直觀驗證了索引與聯接策略對資源消耗的決定性影響，為 SQL 調優提供實證依據。