SQL Server查询持续选用错误索引致性能下降求助

有一条应用自动生成的查询，SQL Hash固定，但始终选错索引，生成低效执行计划。此前靠清除缓存计划、Query Store绑定最优计划能恢复，但现在仅生效数分钟就失效。已确认索引无碎片，统计信息已更新，且该查询固定返回1行数据。

查询语句 #

SELECT test_.ROWID, 
   test_.* 
FROM ******.test_table test_ 
WHERE test_.STOFCY_0 = @P1 
AND test_.VCRTYP_0 = @P2 
AND test_.VCRNUM_0 = @P3 
AND test_.VCRLIN_0 = @P4 
AND test_.REGFLG_0 <> @P5 
AND test_.QTYSTU_0 < @P6 
ORDER BY test_.STOFCY_0,
   test_.UPDCOD_0,
   test_.ITMREF_0,
   test_.IPTDAT_0 Desc,
   test_.MVTSEQ_0,
   test_.MVTIND_0 
OPTION (FAST 1)

已尝试无效的方案 #

• 重建索引+更新统计信息
• 清除错误计划后用Query Store强制绑定最优计划（仅生效数分钟）
• 确认SQL优化器存在行数估计偏差
• 无法修改查询（应用自动生成）
• 仅指定索引能保证性能
• 已在原索引中加入WHERE子句列，但仍无法强制使用

指定索引DDL #

CREATE UNIQUE NONCLUSTERED INDEX [test_table_index] ON [test_table]
(
    [STOFCY_0] ASC,
    [VCRTYP_0] ASC,
    [VCRNUM_0] ASC,
    [VCRLIN_0] ASC,
    [REGFLG_0] ASC,
    [QTYSTU_0] ASC,
    [UPDCOD_0] ASC,
    [ITMREF_0] ASC,
    [IPTDAT_0] DESC,
    [MVTSEQ_0] ASC,
    [MVTIND_0] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

1. 优化索引结构，匹配查询逻辑 #

当前索引把非等值过滤列（REGFLG_0、QTYSTU_0）放在了等值匹配列之后，优化器对这类列的索引利用率极低。调整索引顺序，优先放置等值过滤列，再放排序列，最后是非等值过滤列，同时添加INCLUDE覆盖查询所需列，避免键查找：

CREATE UNIQUE NONCLUSTERED INDEX [test_table_index_optimized] ON [test_table]
(
    -- 等值过滤列（WHERE中完全匹配的条件）
    [STOFCY_0] ASC,
    [VCRTYP_0] ASC,
    [VCRNUM_0] ASC,
    [VCRLIN_0] ASC,
    -- 排序列（完全匹配ORDER BY顺序）
    [UPDCOD_0] ASC,
    [ITMREF_0] ASC,
    [IPTDAT_0] DESC,
    [MVTSEQ_0] ASC,
    [MVTIND_0] ASC,
    -- 非等值过滤列
    [REGFLG_0] ASC,
    [QTYSTU_0] ASC
)
INCLUDE (ROWID) -- 覆盖SELECT需要的列，消除键查找开销
WITH (DROP_EXISTING = ON, PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];

优化后的索引能直接满足过滤、排序需求，无需额外操作，从根源减少优化器选错计划的可能。

2. 用计划指南强制索引（比Query Store更稳定） #

因为无法修改查询，计划指南是强制索引的可靠方案，不会像Query Store那样容易失效：

-- 创建计划指南，需确保@stmt与应用生成的查询完全一致（空格、大小写都要匹配）
EXEC sp_create_plan_guide 
    @name = N'Guide_test_table_query',
    @stmt = N'SELECT test_.ROWID, test_.* FROM ******.test_table test_ WHERE test_.STOFCY_0 = @P1 AND test_.VCRTYP_0 = @P2 AND test_.VCRNUM_0 = @P3 AND test_.VCRLIN_0 = @P4 AND test_.REGFLG_0 <> @P5 AND test_.QTYSTU_0 < @P6 ORDER BY test_.STOFCY_0, test_.UPDCOD_0, test_.ITMREF_0, test_.IPTDAT_0 Desc, test_.MVTSEQ_0, test_.MVTIND_0 OPTION (FAST 1)',
    @type = N'SQL',
    @module_or_batch = NULL,
    @params = N'@P1 varchar(10), @P2 varchar(2), @P3 varchar(20), @P4 int, @P5 varchar(1), @P6 decimal(18,2)', -- 替换为实际参数类型
    @hints = N'OPTION (TABLE HINT(test_, INDEX(test_table_index)))';

如果计划指南不生效，检查@stmt是否完全匹配应用生成的SQL，参数类型是否正确。

3. 修复参数嗅探问题 #

参数化查询可能因参数嗅探导致计划偏差，可通过以下方式解决：

• 在计划指南中添加OPTIMIZE FOR UNKNOWN，让优化器基于平均统计信息生成计划：

  EXEC sp_create_plan_guide 
      @name = N'Guide_test_table_query',
      @stmt = N'[原查询语句]',
      @type = N'SQL',
      @params = N'[参数定义]',
      @hints = N'OPTION (TABLE HINT(test_, INDEX(test_table_index)), OPTIMIZE FOR UNKNOWN)';
  

• 数据库级别禁用参数嗅探（需评估对其他查询的影响）：

  ALTER DATABASE [你的数据库名] SET PARAMETER_SNIFFING OFF;
  

4. 检查Query Store配置 #

如果仍想使用Query Store，确认其配置是否导致计划被提前清理：

• 执行SELECT * FROM sys.database_query_store_options查看配置
• 增大MAX_STORAGE_SIZE_MB避免计划因存储空间不足被删除
• 设置QUERY_CAPTURE_MODE = ALL确保该查询计划被完整捕获
• 延长RETENTION_DAYS增加计划保留时间

内容的提问来源于stack exchange，提问作者Rachit Gupta