參數化查詢導致 SQL Server 篩選索引失效問題分析

現象重現

建立含大量資料的測試表，於 STATUS=1 建立篩選索引。直接查詢 WHERE STATUS=1 可正確使用索引求；但改用 sp_executesql 參數化查詢時，優化器改為掃描叢集索引，篩選索引失效。

原因分析

參數化查詢編譯計畫時，優化器不知道參數實際值，無法判斷是否符合篩選條件，故保守選擇全表掃描或叢集索引掃描。

解決方案

• 使用 OPTION (RECOMPILE) 強制重新編譯，嚐試參數值。
• 改用動態 SQL 字串拼接（注意 SQL 注入風險）。
• 將篩選條件加入索引鍵值（如 INCLUDE(STATUS)），但無法完全替代篩選索引優勢。

測試腳本

DROP TABLE T_TEST;
CREATE TABLE T_TEST( ID INT IDENTITY PRIMARY KEY, STATUS BIT );
INSERT INTO T_TEST SELECT 0 GO 100000;
INSERT INTO T_TEST SELECT 1;
CREATE INDEX IXF_T_TEST_STATUS ON T_TEST(STATUS) WHERE STATUS=1;
UPDATE STATISTICS T_TEST WITH FULLSCAN;
DBCC FREEPROCCACHE;

-- 正常查詢（使用篩選索引）
SELECT * FROM T_TEST WHERE STATUS=1;

-- 參數化查詢（失效）
EXEC sp_executesql N'SELECT * FROM T_TEST WHERE STATUS=@STATUS', N'@STATUS BIT', 1;