SQL Server 效能調校之四：四個常見陷阱解析
在調校 SQL Server 查詢效能時，有些問題容易被忽略，卻也容易造成嚴重的效能退化：Missing Index、Implicit Conversion、Spill to TempDB、以及 Residual Predicate；本篇整理這四種問題的成因、症狀與修正方式，並說明彼此之間容易混淆的關鍵差異。 Missing Index（缺少索引） 成因：查詢的篩選欄位（WHERE、JOIN 條件）未建立對應索引，導致 SQL Server 必須掃描整張資料表（Table Scan 或 Clustered Index Scan）才能找到符合的資料列。 症狀： 執行計畫出現 Table Scan 或 Clustered Index Scan 節點。 Logical reads 數量遠超預期。 CPU 與磁碟 IO 長期偏高。 偵測方式：執行計畫中會出現綠色的「遺漏索引」提示文字；亦可查詢 sys.dm_db_missing_index_details 取得建議清單。 修正建議：針對高頻查詢的篩選欄位建立索引，並善用 INCLUDE 子句將查詢所需的非索引鍵欄位一併納入，形成 Covering Index，避免額外的 Key Lookup。 1CREATE INDEX IX_Orders_CustDate 2 ON Orders (CustomerID, OrderDate) 3 INCLUDE (TotalAmount); Implicit Conversion（隱含型別轉換） 成因：當查詢參數或比較值的資料型別與欄位型別不符時，SQL Server 會自動進行隱含型別轉換；轉換作業發生在欄位端，使得索引的 Seek 能力喪失，退化為 Scan。 症狀： 明明已建立索引，執行計畫仍顯示 Index Scan 而非 Index Seek。 執行計畫節點出現黃色警告，標示 CONVERT_IMPLICIT。 偵測方式：檢查執行計畫節點上的警告圖示，或透過 SET STATISTICS IO ON 觀察 Logical reads 是否異常偏高。 修正建議：確保應用程式傳入的參數型別與資料表欄位型別完全一致；使用 ORM 框架時須特別注意 nvarchar 與 varchar 的混用，以及資料庫 Collation 設定是否統一。 1-- 錯誤：欄位為 int，傳入字串導致隱含轉換 2WHERE CustomerID = '12345' 3 4-- 正確：型別一致，索引可正常 Seek 5WHERE CustomerID = 12345 Spill to TempDB（記憶體溢出至暫存資料庫） 成因：執行 Sort、Hash Match、或 Window Function 等需要工作記憶體的運算時，若 SQL Server 低估了資料量（通常源自過期的統計資料），分配的 Memory Grant 不足，多餘的中間資料便會溢出（Spill）至 TempDB 磁碟。 症狀： 執行計畫的 Sort 或 Hash Match 節點出現警告圖示。 TempDB 磁碟 IO 明顯飆升。 相同查詢的執行時間不穩定、變異大。 偵測方式：查看執行計畫節點屬性中的 Warning > SpillLevel；或查詢 sys.dm_exec_query_stats 的 total_spills 欄位找出高溢出查詢。 修正建議：這個問題通常是統計資料過舊，建議定期執行 UPDATE STATISTICS；此外，可以透過建立索引讓資料預先排序，減少 Sort 運算的需求，或將大量資料的操作拆分成批次處理。 1-- 更新統計資料 2UPDATE STATISTICS Orders WITH FULLSCAN; 3 4-- 建立索引預先排序，減少 Sort 需求 5CREATE INDEX IX_Orders_Date 6 ON Orders (OrderDate); Residual Predicate（殘餘篩選條件） 成因：Index Seek 只能用索引的前導鍵欄位（Seek Predicate）快速定位資料範圍，查詢條件中不屬於前導鍵的欄位，會在 Storage Engine 層逐列再次過濾（Predicate），稱為殘餘謂詞（Residual Predicate）。 症狀： 執行計畫中有 Index Seek，表面上看起來正常。 Seek 節點的 Rows Read 遠大於 Actual Rows，顯示大量資料列被讀取後又被篩掉。 Logical reads 偏高但不易察覺。 偵測方式：點開執行計畫中 Index Seek 節點的屬性，區分 Seek Predicates（有效利用索引）與 Predicates（殘餘過濾）兩個欄位的內容。 修正建議：將殘餘條件欄位加入複合索引的鍵欄位，或納入 INCLUDE 清單，調整索引欄位順序以符合查詢的過濾邏輯。 1-- 原索引只有 OrderDate, Status 成為殘餘條件 2-- 調整為複合索引以消除殘餘條件 3CREATE INDEX IX_Orders_DateStatus 4 ON Orders (OrderDate, Status); 容易混淆的差異 Missing Index vs Residual Predicate：前者是「完全沒有索引」，後者是「有索引但 Seek 只用了部分欄位」；看到執行計畫有 Index Seek 不代表沒有問題，需要進一步比對 Rows Read 與 Actual Rows 的差距。 Implicit Conversion：它會靜默破壞現有索引，讓原本正常的 Seek 退化成 Scan，執行計畫只會出現一個小黃警告，極易被忽略；ORM 框架是最常見的來源，尤其是字串型別混用或 Collation 不一致的情境。 Spill to TempDB：與其他三者性質不同，它不是索引問題，而是「查詢工作記憶體不足」的問題；根本原因通常是統計資料過舊，光是建立索引並無法解決，必須從統計資料更新與查詢設計兩方面著手。