SQL Server高峰使用时段性能急剧下降的最优处理方案是什么

实时排查定位根因（高峰故障发生时优先执行） #

• 抓系统等待类型统计：执行SELECT * FROM sys.dm_os_wait_stats ORDER BY wait_time_ms DESC，重点关注PAGEIOLATCH_xx（IO瓶颈）、CXPACKET（并行查询冲突）、RESOURCE_SEMAPHORE（内存不足查询排队）、LCK_M_xx（锁阻塞）四类高频异常等待，先确定资源瓶颈方向
• 定位高消耗查询：执行SELECT TOP 10 SUBSTRING(qt.text, (qs.statement_start_offset/2)+1, ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(qt.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)+1) AS 执行语句, qs.total_worker_time/qs.execution_count AS 平均CPU耗时, qs.total_logical_reads/qs.execution_count AS 平均逻辑读 FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt ORDER BY qs.total_worker_time DESC，优先处理单条消耗高、执行频次高的查询，这类查询平峰时负载低无感知，高峰并发上来会快速占满系统资源
• 排查阻塞链：如果等待类型以锁等待为主，执行SELECT * FROM sys.dm_tran_locks快速定位持有排他锁时间过长的会话，确认是非核心业务会话可直接终止，快速恢复资源

平峰前置优化（避免后续重复出现故障） #

• 索引优化：针对高频查询的筛选、关联、排序条件创建覆盖索引，消除全表扫描、键查找等高开销操作，同时清理重复、长期未使用的无效索引，降低数据写入的额外开销
• 执行计划稳定性优化：解决参数嗅探问题，对执行计划波动大的语句可添加OPTION (RECOMPILE)查询提示，或者强制绑定已知的最优执行计划，避免平峰生成的执行计划不适配高峰的数据访问特征
• 基础配置调优：
  • 最大并行度（MAXDOP）调整为单个NUMA节点的CPU核心数，避免并行查询过度抢占CPU资源引发调度冲突
  • 提前预分配数据文件、事务日志文件的空间，关闭事务日志的小容量自动增长配置，避免高峰时段文件扩容引发的IO阻塞
  • 开启即时文件初始化权限，大幅降低数据文件扩容的耗时
• 架构层面优化：读请求占比较高的场景可搭建1-N个只读副本，将报表、历史数据查询、非核心业务的读请求切到只读节点，降低主库的高峰压力
• 限流预案梳理：提前标记非核心业务的请求接口，极端高峰时可临时降级非核心请求，优先保障核心业务的资源供给

高峰应急快速恢复手段 #

• 优先终止长时间运行的非核心查询、大事务，快速释放CPU、IO、锁资源
• 若触发了内存不足、IO打满的情况，可临时将非核心的定时调度任务暂停，待高峰过后再恢复执行

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