关闭AUTO_CREATE_STATISTICS后SQL Server预估行数来源及锁定问题

核心结论 #

当关闭AUTO_CREATE_STATISTICS且目标列无手动创建的统计信息时，SQL Server的预估行数基于执行计划生成时的表总行数，通过「平方根启发式规则」计算过滤后的返回行数；执行查询后计划会被存入缓存，后续复用缓存计划时不会自动更新预估数值，除非触发计划重编译。

实验现象的详细解释 #

预估行数的计算逻辑 #

你的实验中LastName列既没有自动生成统计（因AUTO_CREATE_STATISTICS关闭），也未手动创建统计信息，SQL Server对这类无统计的列执行过滤查询时，会采用默认启发式逻辑：

• 生成执行计划时读取当前表的总行数；
• 对于无法判断分布的匹配值（比如'blah'不存在于数据中），用「总行数的平方根」预估返回行数，这就是你看到200→14.142、400→20的原因。

执行查询后预估数值锁定的原因 #

执行查询后，该执行计划会被存入SQL Server计划缓存。只要缓存未失效，后续生成预估计划时会直接复用已有计划，其中的预估行数是计划生成时固化的数值，不会随表行数变化自动更新。

要触发计划重新编译（从而读取最新行数计算预估），需要满足特定条件：

• 表结构或索引发生变更；
• 显式执行sp_recompile 'TestTable'；
• 行数变化达到「重编译阈值」：小表（行数<500）为500行+20%增量，大表（行数≥500）为20%增量。你的实验中从400到600的增量虽达50%，但因无列统计，SQL Server未触发重编译，因此缓存计划继续生效。

UPDATE STATISTICS无效的原因 #

UPDATE STATISTICS TestTable仅会更新：

• 表的整体元数据统计（如总行数、页数）；
• 主键索引的统计信息。
  但LastName列仍无单独统计，且缓存计划未被重编译，因此预估数值不会更新。

查看固化行数的方法 #

固化在缓存计划中的总行数可通过动态管理视图（DMV）查询：

1. 查看执行计划XML中的预估信息

   SELECT 
       cp.plan_handle,
       qp.query_plan
   FROM sys.dm_exec_cached_plans cp
   CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp
   WHERE qp.query_plan LIKE '%TestTable%LastName%';
   

   从返回的XML中可找到EstimatedRows等节点，对应计划生成时的预估数值。

2. 查看计划属性中的行数

   SELECT 
       pa.attribute,
       pa.value
   FROM sys.dm_exec_cached_plans cp
   CROSS APPLY sys.dm_exec_plan_attributes(cp.plan_handle) pa
   WHERE cp.objtype = 'Adhoc' 
     AND pa.attribute = 'rows';
   

   rows属性的值即为计划生成时使用的表总行数。

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