執行計畫原本使用 Index Seek(索引查尋),卻變成 Index Scan(索引掃描)甚至 Table Scan,導致查詢效能大幅下降。以下整理四大主因與對策。
-- NationalIDNumber 為 NVARCHAR,輸入數值常數導致隱含轉換 SELECT NationalIDNumber, LoginID FROM HumanResources.Employee WHERE NationalIDNumber = 112457891; -- 執行計畫顯示 Index Scan + CONVERT_IMPLICIT

WHERE NationalIDNumber = N'112457891'。WHERE NationalIDNumber = CAST(112457891 AS NVARCHAR(15))。
並非所有隱含轉換都會導致掃描,詳細型別對照表請參考微軟文件 Implicit Conversions that cause Index Scans。
SARG (Search ARGument) 指優化器能利用索引查尋的條件形式。以下寫法會破壞 SARG:
| 類型 | 錯誤寫法 | 正確寫法 |
|---|---|---|
| 索引欄位套用函數 | WHERE col LIKE 'ABC%' | |
| 索引欄位參與運算 | WHERE col < 90 | |
| 前導萬用字元 LIKE | 改用全文索引或反向索引 | |
| 否定運算子 | 改寫為 WHERE col < 'X' OR col > 'X'(視資料分布) |
實測對比:
-- 掃描 SELECT * FROM Person.Person WHERE BusinessEntityID + 10 < 260; -- 查尋 SELECT * FROM Person.Person WHERE BusinessEntityID < 250;

當優化器預估回傳列數超過臨界點,會放棄「非叢集索引查尋 + Key Lookup」改走叢集索引掃描或表掃描。
UPDATE STATISTICS tbl WITH FULLSCAN。OPTION (RECOMPILE)、OPTIMIZE FOR UNKNOWN 或查詢存儲強制計畫。SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
DECLARE @dbname SYSNAME = QUOTENAME(DB_NAME());
WITH XMLNAMESPACES (DEFAULT 'https://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT
stmt.value('(@StatementText)[1]', 'varchar(max)') AS StatementText,
t.value('(ScalarOperator/Identifier/ColumnReference/@Schema)[1]', 'varchar(128)') AS SchemaName,
t.value('(ScalarOperator/Identifier/ColumnReference/@Table)[1]', 'varchar(128)') AS TableName,
t.value('(ScalarOperator/Identifier/ColumnReference/@Column)[1]', 'varchar(128)') AS ColumnName,
ic.DATA_TYPE AS ConvertFrom,
t.value('(@DataType)[1]', 'varchar(128)') AS ConvertTo,
query_plan
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
CROSS APPLY qp.query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') batch(stmt)
CROSS APPLY stmt.nodes('.//Convert[@Implicit="1"]') n(t)
JOIN INFORMATION_SCHEMA.COLUMNS ic
ON QUOTENAME(ic.TABLE_SCHEMA) = t.value('(ScalarOperator/Identifier/ColumnReference/@Schema)[1]', 'varchar(128)')
AND QUOTENAME(ic.TABLE_NAME) = t.value('(ScalarOperator/Identifier/ColumnReference/@Table)[1]', 'varchar(128)')
AND ic.COLUMN_NAME = t.value('(ScalarOperator/Identifier/ColumnReference/@Column)[1]', 'varchar(128)')
WHERE t.exist('ScalarOperator/Identifier/ColumnReference[@Database=sql:variable("@dbname")][@Schema!="[sys]"]') = 1;