SQL Server多字段聚集索引统计信息导致预估行数错误问题求助

根因说明 #

你遇到的问题属于SQL Server默认采样统计的固有局限性：当复合索引的前缀列组合的distinct值较多时，默认采样率下采集的样本不足以准确计算多列组合的密度值，导致基于平均密度的基数预估出现大幅偏差。你测试中使用局部变量的查询编译时无法拿到具体参数值，只会使用统计信息存储的平均密度乘以总行数计算预估行数，因此密度值错误会直接导致预估行数不准。

可行解决方案 #

• 方案1：持久化统计信息采样率（适用SQL Server 2016 SP1 CU4及以上版本）
  你可以为目标表的统计信息设置永久生效的采样率，后续自动/手动更新统计时都会沿用该配置，无需每次手动指定FULLSCAN。如果担心全扫描开销过高，也可以设置一个足够保证多列密度计算准确的高采样率，比如30%：

  -- 持久化全扫描采样率
  UPDATE STATISTICS dbo.glp_test WITH FULLSCAN, PERSIST_SAMPLE_PERCENT = ON;
  -- 或持久化指定比例的采样率
  UPDATE STATISTICS dbo.glp_test WITH SAMPLE 30 PERCENT, PERSIST_SAMPLE_PERCENT = ON;
  

• 方案2：创建专用多列统计信息（全版本适用）
  针对你高频过滤的NagId+Lp列组合单独创建统计信息，该统计可以独立于索引统计更新，维护开销远低于全表全扫描更新统计：

  -- 创建双列组合统计
  CREATE STATISTICS st_gltest_nagid_lp ON dbo.glp_test (NagId, Lp) WITH FULLSCAN;
  

  你可以将该统计的更新操作加入轻量的定期维护任务，比如每天执行一次高采样率更新，即可长期保证基数预估准确。

• 方案3：调整统计自动更新触发阈值
  大表默认20%行变化才触发自动统计更新的阈值过高，会导致统计信息长期过时。你可以开启跟踪标志2371（适用于SQL Server 2008 R2 SP1及以上版本），对大表启用动态的统计更新阈值：表行数越多，触发自动更新所需的行变化比例越低，让统计信息更新更及时。
  也可以在表级别开启增量统计更新（SQL Server 2014及以上支持）：

  ALTER DATABASE 你的库名 SET AUTO_UPDATE_STATISTICS_INCREMENTAL = ON;
  

• 方案4：查询级别的优化适配
  如果不方便调整统计维护策略，可以对该查询做针对性适配：

  1. 将查询封装为存储过程使用参数化执行，利用参数嗅探拿到首次执行时的真实行数生成正确执行计划
  2. 对已知返回行数的查询添加行计数提示，强制优化器使用接近真实值的预估：

  declare @Nagid bigint, @Lp bigint
  select * from dbo.glp_test where NagID = @NagId and Lp = @Lp
  OPTION (USE HINT ('ASSUME_MIN_SELECTIVITY_FOR_FILTER_ESTIMATES'), FAST 30);
  

内容的提问来源于stack exchange，提问作者Grzegorz Łyp