列存储索引缺失异常：关联CTE/表变量时索引提示失效问题咨询

问题背景 #

我有一张带有列存储索引TestIndex的[dbo].[SalesBudgetMonths]表：

• 当与物理表dbo.Sellers在索引列[Seller]上关联时，执行计划显示两张表均为索引扫描，无异常，SQL代码如下：

;WITH s1 AS 
(
    SELECT 232 AS Id
)
SELECT
    sbm.[Type]
FROM 
    [dbo].[SalesBudgetMonths] sbm WITH (INDEX(TestIndex))
JOIN 
    dbo.Sellers s ON s.Id = sbm.[Seller] 
-- JOIN s1 ON s1.Id = sbm.[Seller]
GROUP BY 
    sbm.[Type];

• 但当与CTE或表变量关联时，SQL Server Management Studio会提示索引缺失，SQL代码如下：

;WITH s1 AS 
(
     SELECT 232 AS Id
)
SELECT
    sbm.[Type]
FROM 
    [dbo].[SalesBudgetMonths] sbm WITH (INDEX(TestIndex))
-- JOIN dbo.Sellers s ON s.Id = sbm.[Seller] 
JOIN 
    s1 ON s1.Id = sbm.[Seller]
GROUP BY 
    sbm.[Type];

索引定义 #

列存储索引TestIndex的定义如下（列顺序与表一致）：

CREATE NONCLUSTERED COLUMNSTORE INDEX [TestIndex] ON [dbo].[SalesBudgetMonths]
( 
       [Year]
      ,[Currency]
      ,[Department]
      ,[BudgetType]
      ,[Business]
      ,[Ranking]
      ,[Seller]
      ,[CustomerService]
      ,[Supplier]
      ,[SupplierGroup]
      ,[Customer]
      ,[CustomerGroup]
      ,[ItemShortCode]
      ,[Month]
      ,[Type]
      ,[Budget Year]
      ,[Sales]
      ,[Sales LY]
      ,[Budget Jan Year]
      ,[Sales Jan]
      ,[Sales LY Jan]
      ,[Budget Feb Year]
      ,[Sales Feb]
      ,[Sales LY Feb]
      ,[Budget Mar Year]
      ,[Sales Mar]
      ,[Sales LY Mar]
      ,[Budget Apr Year]
      ,[Sales Apr]
      ,[Sales LY Apr]
      ,[Budget May Year]
      ,[Sales May]
      ,[Sales LY May]
      ,[Budget Jun Year]
      ,[Sales Jun]
      ,[Sales LY Jun]
      ,[Budget Jul Year]
      ,[Sales Jul]
      ,[Sales LY Jul]
      ,[Budget Aug Year]
      ,[Sales Aug]
      ,[Sales LY Aug]
      ,[Budget Sep Year]
      ,[Sales Sep]
      ,[Sales LY Sep]
      ,[Budget Oct Year]
      ,[Sales Oct]
      ,[Sales LY Oct]
      ,[Budget Nov Year]
      ,[Sales Nov]
      ,[Sales LY Nov]
      ,[Budget Dec Year]
      ,[Sales Dec]
      ,[Sales LY Dec]
      ,[Budget Year YTD]
      ,[Sales YTD]
      ,[Sales LY YTD]
      ,[Budget Year YTD MONTH]
      ,[Sales YTD  MONTH]
      ,[Sales LY YTD  MONTH]
)WITH (DROP_EXISTING = OFF, COMPRESSION_DELAY = 0, DATA_COMPRESSION = COLUMNSTORE) ON [PRIMARY]
GO

咨询问题 #

1. 为何出现此情况？即使已通过WITH (INDEX(TestIndex))指定索引
2. 是否有解决方案？
3. 若执行计划显示对索引而非表数据执行查找/扫描，是否无需关注此类提示？

解答 #

1. 问题原因 #

SQL Server的索引缺失提示基于查询优化器的成本估算逻辑生成：

• 关联物理表dbo.Sellers时，优化器能获取该表的统计信息（行数、数据分布等），可准确评估使用列存储索引TestIndex扫描的成本，判断现有索引足够，不会触发提示。
• 关联CTE或表变量时，这类对象默认无统计信息（表变量在SQL Server 2019及之前无统计信息，CTE是临时结果集，优化器无法获取其数据分布细节）。优化器无法准确估算关联后的过滤效果，会假设需要更高效的索引支持Seller列的查找，因此触发缺失提示——哪怕你强制指定了列存储索引，优化器的提示逻辑仍会基于它对“理想执行路径”的判断发出警告。

另外，列存储索引面向批量扫描优化，优化器面对小数据集（比如CTE仅一行数据）时，可能认为行存储B树索引更适合点查找，但你强制指定了列存储索引，导致提示逻辑和实际执行计划出现偏差。

2. 解决方案 #

可根据场景选择以下方法：

• 给表变量添加统计信息（SQL Server 2019及以上）：在表变量定义后创建统计信息，示例：

  DECLARE @s1 TABLE (Id INT);
  INSERT INTO @s1 VALUES (232);
  CREATE STATISTICS stats_s1_Id ON @s1(Id);
  

  让优化器获取数据分布，避免误判索引需求。
• 用临时表替代CTE/表变量：临时表会自动生成统计信息，优化器能更准确评估执行成本，消除错误提示。
• 禁用索引缺失提示：若确认执行计划最优，可在SSMS中关闭提示（工具→选项→查询执行→SQL Server→高级→取消勾选“显示缺失索引建议”），但这是全局设置，会影响所有查询。
• 添加查询重编译提示：在查询末尾加OPTION (RECOMPILE)，让优化器基于当前数据重新生成执行计划，消除错误提示：

  ;WITH s1 AS 
  (
       SELECT 232 AS Id
  )
  SELECT
      sbm.[Type]
  FROM 
      [dbo].[SalesBudgetMonths] sbm WITH (INDEX(TestIndex))
  JOIN 
      s1 ON s1.Id = sbm.[Seller]
  GROUP BY 
      sbm.[Type]
  OPTION (RECOMPILE);
  

3. 是否需要关注提示 #

如果执行计划确实使用了指定的列存储索引（扫描/查找），且查询性能符合预期，这类提示可以忽略。因为提示是优化器基于“理想情况”的建议，但实际你已经通过强制索引指定了更适合当前场景的执行路径，优化器的提示逻辑未跟上实际执行计划的选择。

但需注意：若后续CTE/表变量的数据量变大，需重新评估索引是否合适，避免因忽略提示导致性能下降。

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