SQL Server突发性能骤降：简单COUNT查询耗时异常排查求助

可能的原因 #

• 统计信息过期：SQL Server依赖统计信息生成最优执行计划，若统计信息未随数据更新，可能导致选择低效的执行计划（如全表扫描而非索引扫描）。
• 索引异常：原本支持快速COUNT(*)的索引（聚集索引或小型非聚集索引）被意外删除、禁用或损坏，迫使查询只能进行全表扫描。
• 阻塞/死锁：其他会话对AllAppointments表执行长时间写操作（如批量更新、删除），导致COUNT查询被阻塞等待。
• 服务器资源耗尽：CPU、内存、磁盘IO被其他进程占用，查询无法获得足够资源完成执行。
• 执行计划缓存失效：错误的执行计划被缓存，或缓存清空后生成了低效的执行计划。
• 表/索引碎片过多：数据页或索引页碎片化严重，查询需要扫描更多物理页，增加IO耗时。

排查步骤 #

1. 查看执行计划
   在SSMS中开启「包括实际执行计划」（快捷键Ctrl+M）后执行查询，观察是否采用全表扫描。也可通过代码查看：

   SET SHOWPLAN_XML ON;
   SELECT COUNT(*) FROM AllAppointments;
   SET SHOWPLAN_XML OFF;
   

   若显示全表扫描，说明索引或统计信息存在问题。

2. 检查统计信息状态
   查询表的统计信息更新时间与数据分布情况：

   DBCC SHOW_STATISTICS('AllAppointments', ALL)
   

   若统计信息的更新日期远早于最近的数据变更，说明统计信息已过期。

3. 排查阻塞会话
   查看当前是否有会话阻塞该查询：

   SELECT * FROM sys.dm_exec_requests WHERE blocking_session_id <> 0
   

   或使用sp_who2查看会话状态，定位阻塞源。

4. 检查服务器资源
   通过SSMS的「活动监视器」查看CPU、内存、磁盘IO的使用率，确认是否存在资源瓶颈。

5. 验证索引状态
   检查表的索引是否存在、启用：

   SELECT name, index_id, is_disabled, is_hypothetical 
   FROM sys.indexes 
   WHERE object_id = OBJECT_ID('AllAppointments')
   

6. 检测表/索引碎片
   查询碎片化程度：

   SELECT * FROM sys.dm_db_index_physical_stats(
       DB_ID(), OBJECT_ID('AllAppointments'), NULL, NULL, 'DETAILED'
   )
   

   若avg_fragmentation_in_percent超过30%，说明碎片严重。

修复方案 #

• 更新统计信息
  强制更新表的统计信息，确保执行计划基于最新数据分布：

  UPDATE STATISTICS AllAppointments WITH FULLSCAN;
  

• 修复索引问题

  • 若索引缺失：创建小型非聚集索引（COUNT(*)可利用最小的索引快速计数）：

    CREATE NONCLUSTERED INDEX IX_AllAppointments_Count ON AllAppointments(Id); -- 替换为表中任意非空列
    

  • 若索引损坏/碎片严重：
    • 碎片率>30%时重建索引：

      ALTER INDEX ALL ON AllAppointments REBUILD;
      

    • 碎片率10%-30%时重新组织索引：

      ALTER INDEX ALL ON AllAppointments REORGANIZE;
      

  • 若索引被禁用：启用索引：

    ALTER INDEX 索引名 ON AllAppointments REBUILD;
    

• 解决阻塞问题
  若阻塞源是无效的长时间操作，可终止会话（谨慎操作，避免影响业务）：

  KILL 阻塞会话ID;
  

  若为正常业务操作，等待其完成即可。

• 释放/优化服务器资源

  • 关闭占用资源的非必要进程；
  • 调整SQL Server内存配置（如设置合理的max server memory）；
  • 若硬件瓶颈无法临时缓解，考虑升级服务器资源。

• 重置执行计划缓存
  让表的查询重新生成执行计划：

  EXEC sp_recompile 'AllAppointments';
  

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