Azure SQL存储过程执行超时问题及解决后原因咨询

问题回顾 #

在Azure SQL副本库执行大型存储过程时，出现以下间歇性超时现象：

• 存储过程平均执行时长30-40秒，但仅在查询最近1天的数据时触发超时
• 超时具有时效性：昨日查询2023-11-14 00:00至2023-11-15 23:59:59时段超时，今日查询该时段正常，但查询2023-11-15 00:00至2023-11-16 23:59:59时段再次超时
• 将日期范围扩展至2天（如2023-11-14至2023-11-16）时，存储过程执行正常
• 直接在API中执行原生SQL查询任意时段均无问题
• 同时有同步服务从其他数据源向该副本库同步数据
• 最终通过将存储过程拆分为两步解决问题：第一步把筛选后的字段存入临时表，第二步基于临时表完成聚合和排序

相关执行语句示例：

昨日超时语句 #

declare
    @stratDate datetime2 = '2023-11-14 00:00',
    @endDate datetime2 = '2023-11-15 23:59:59'
exec procedure_name @stratDate, @endDate

今日超时语句 #

declare
    @stratDate datetime2 = '2023-11-15 00:00',
    @endDate datetime2 = '2023-11-16 23:59:59'
exec procedure_name @stratDate, @endDate

正常执行的扩展范围语句 #

declare
    @stratDate datetime2 = '2023-11-14 00:00',
    @endDate datetime2 = '2023-11-16 23:59:59'
exec procedure_name @stratDate, @endDate

核心原因分析 #

1. 参数嗅探引发的执行计划偏差 #

存储过程的执行计划会在首次编译时基于传入的参数生成。当查询单日数据时，若首次编译用的是数据量较小的日期参数，生成的执行计划可能无法适配后续数据量较大的单日范围；反之，若首次编译用的是大数据量参数，小数据量查询也会沿用低效计划。而原生SQL每次执行都会重新生成执行计划，自然避开了参数嗅探的问题。此外，副本库的统计信息可能因同步延迟或数据高频更新，导致优化器对单日数据的行数估计错误，进一步加剧计划偏差。

2. 同步服务带来的锁阻塞 #

同步服务持续向副本库写入最新数据，会对最近1天的表数据持有锁（比如行锁、页锁）。原存储过程的单一大查询需要扫描或访问这些被锁定的数据时，会遭遇阻塞，最终触发超时。当扩展日期范围时，查询涉及的数据分布更广，锁冲突的概率大幅降低；而拆分到临时表后，第一步筛选数据的操作可能更快获取锁，后续聚合排序直接在临时表上执行，完全隔离了原表的锁阻塞影响。

3. 统计信息过时或不准确 #

最近1天的数据处于动态变化状态（同步服务持续写入），Azure SQL的自动统计信息更新可能存在延迟，导致优化器无法准确判断单日数据的行数、分布情况，进而选择了低效的执行计划（比如用嵌套循环代替哈希连接，或未使用合适的索引）。

临时表解决方案生效的原理 #

• 规避参数嗅探：拆分后的两步查询，第二步基于临时表的查询会重新编译执行计划，不再受原存储过程首次编译时参数的影响，确保每次查询都能生成适配当前数据的计划。
• 隔离锁冲突：临时表是会话级别的私有对象，写入临时表后，后续的聚合排序操作不再访问原表，彻底避开了同步服务对原表数据的锁阻塞。
• 获取准确统计信息：临时表的数据是筛选后的子集，优化器会基于临时表的实时统计信息生成执行计划，避免了原表统计信息过时带来的估计错误。
• 降低查询复杂度：将单一大查询拆分为两步，简化了优化器的决策逻辑，减少了复杂查询中可能出现的计划选择失误。

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