「表掃描」是執行計畫中最常見也最令人頭痛的運算子之一。本文從儲存引擎底層的資料頁結構出發,配合 DBCC IND、DBCC PAGE 實戰演示,帶您看透表掃描的真實運作機制,並掌握避免全表掃描的核心策略。
當查詢無可用索引、或優化器判定掃描全表成本低於索引查找時,SQL Server 會對 Heap(堆積表) 或 Clustered Index(叢集索引)的葉層 進行逐頁、逐列掃描。執行計畫會顯示 Table Scan(堆積表)或 Clustered Index Scan(叢集索引)。
CREATE TABLE dbo.Person (
ID INT NOT NULL,
Name VARCHAR(20) NOT NULL
); -- 故意不建立任何索引,形成 Heap
INSERT INTO dbo.Person VALUES (1, 'Alice'), (2, 'Bob'), (3, 'Charlie');
執行 SELECT * FROM dbo.Person WHERE Name = 'Bob';,執行計畫顯示 Table Scan。
-- 開啟追蹤旗標 3604 將輸出導向用戶端
DBCC TRACEON(3604);
GO
-- 查詢 Person 表的所有頁面
DBCC IND('TestDB', 'Person', -1);
GO
輸出關鍵欄位:
PageFID、PagePID:檔案號與頁號(如 1:78)。IAMFID、IAMPID:對應的 IAM 頁;若為 NULL 則該列本身是 IAM 頁。PageType:1 = 資料頁,10 = IAM 頁。-- 輸出選項 3:頁首 + 每列詳細欄位值
DBCC PAGE('TestDB', 1, 78, 3);
GO
輸出三大區塊:
| 策略 | 適用場景 | 注意事項 |
|---|---|---|
| 建立非叢集索引覆蓋查詢欄位 | 高選擇性 WHERE、JOIN、ORDER BY 欄位 | 避免過度索引導致寫入放大;使用 INCLUDE 覆蓋非鍵欄位。 |
| 將 Heap 改為叢集索引 | 大多數資料表應有叢集索引(窄、唯一、遞增) | 叢集索引鍵變更會導致非叢集索引維護開銷。 |
| 分區表 | 超大資料表(億級以上),按範圍刪除/存取 | 需配合分區函數/配置,查詢需包含分區欄位才能分區消除。 |
| 資料行存儲索引 | 資料倉庫/OLAP 大量掃描聚合 | SQL Server 2012+,壓縮率高、批次模式執行,但不適合高頻點查/更新。 |
| 統計資料更新 / 重新編譯 | 統計過期導致誤判掃描成本 | UPDATE STATISTICS、OPTION (RECOMPILE)、啟用自動更新統計。 |
| 查詢改寫 | SARGable 條件、避免函數包裹欄位 | WHERE YEAR(CreateDate)=2023 → WHERE CreateDate >= '2023-01-01' AND CreateDate < '2024-01-01'。 |
SELECT
OBJECT_NAME(ips.object_id) AS TableName,
i.name AS IndexName,
ips.index_type_desc,
ips.avg_fragmentation_in_percent,
ips.page_count,
ips.record_count,
ips.avg_page_space_used_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ips
JOIN sys.indexes i ON i.object_id = ips.object_id AND i.index_id = ips.index_id
WHERE ips.index_id > 0 -- 排除 Heap(index_id=0)
ORDER BY ips.avg_fragmentation_in_percent DESC;
avg_fragmentation_in_percent > 30% → ALTER INDEX ... REBUILD。5% < fragmentation < 30% → ALTER INDEX ... REORGANIZE。avg_page_space_used_in_percent 過低 → 考慮調整 FILLFACTOR。表掃描並非絕對之惡——小表、全表聚合、資料行存儲場景下它是最優選擇。關鍵在於識別不必要的大表掃描,透過索引設計、統計維護、查詢改寫將其轉為 Index Seek 或限制掃描範圍。掌握 DBCC IND/PAGE 與 DMV 觀察儲存層實況,是從「會寫 SQL」進階到「懂引擎」的關鍵一步。