Azure SQL Server原查询突发耗尽DTU无响应，改用临时表后恢复求原因

我在Azure上部署了Microsoft SQL Server数据库，此前运行正常的一条查询近期突然耗尽DTU且无返回结果。排查时将查询改写为多个SELECT ... INTO #temp_table语句组成的存储过程后，执行仅需数毫秒。

原查询：

SELECT      T_Primary.Id
FROM        T_Primary
INNER JOIN  T_Secondary ON T_Secondary.Id = T_Primary.T_Secondary_Id
WHERE       (T_Secondary.Path = @path OR T_Secondary.Path Like concat(@path, '\%'))
AND         T_Primary.Probability < 1
AND         T_Primary.Ignore = 0

表与索引信息：

• T_Primary有251464行，T_Secondary有180032行
• T_Primary.T_Secondary_Id存在引用T_Secondary.Id的外键，且有对应索引
• 列定义：

T_Primary.T_Secondary_Id  int            not null
T_Primary.Probability     float          not null
T_Primary.Ignore          bit            not null
T_Secondary.Id            int            not null  identity
T_Secondary.Path          nvarchar(255)  not null

改写后的存储过程：

CREATE PROCEDURE dbo.sp_0001 (@path nvarchar(255) = NULL)
AS
BEGIN
    SET NOCOUNT ON

    SELECT      T_Secondary.id, 
                T_Secondary.path, 
                T_Secondary.name
    INTO        #temp1
    FROM        T_Secondary 
    WHERE       (T_Secondary.Path = @path OR T_Secondary.Path Like concat(@path, '\%'))
    ;

    SELECT      T_Primary.Id,
                T_Primary.[Probability],
                T_Primary.[Ignore]
    INTO        #temp2
    FROM        T_Primary 
    Inner JOIN  #temp1
    on          #temp1.Id = T_Primary.T_Secondary_Id
    ;

    SELECT      Id,
                Probability,
                Ignore
    FROM        #temp2
    WHERE       Probability < 1
    AND         Ignore = 0
    ;

END
GO

核心疑问：

1. 原查询为何突然失效？
2. 是否需要改写所有关联查询以避免该问题再次发生？

一、原查询突然失效的核心原因 #

1. 参数嗅探导致的执行计划复用错误 #

原查询的@path参数如果首次执行时传入的是低基数值（仅匹配少量T_Secondary数据），SQL Server会生成基于该数据量的高效执行计划；后续当传入高基数值（匹配大量数据）时，优化器复用了旧计划，导致执行路径完全不适合新的数据量，最终引发全表扫描、低效连接等操作，耗尽DTU。
而临时表拆分的存储过程将查询拆分为独立步骤，每个步骤的执行计划都是基于临时表的实际数据量实时生成，彻底规避了参数嗅探带来的计划复用问题。

2. 统计信息过期引发的估算偏差 #

近期数据分布的变化（比如T_Secondary.Path的取值分布改变）导致表的统计信息过期，优化器基于过时的统计数据估算过滤后的数据行数，错误地选择了低效的执行路径（比如先扫描T_Primary再关联T_Secondary）。临时表的统计信息是实时生成的，能让优化器做出更准确的执行决策。

3. 谓词顺序导致的索引利用不足 #

原查询的WHERE子句同时包含两张表的过滤条件，优化器可能错误地优先处理T_Primary的过滤，而没有先筛选出T_Secondary的小数据集，导致后续关联的数据量过大。临时表方式强制先执行T_Secondary的高选择性过滤，从根源上减少了关联的数据量。

二、是否需要改写所有关联查询？ #

不需要盲目改写所有关联查询，建议按以下优先级解决问题：

1. 刷新统计信息 #

先执行以下语句更新两张表的统计信息，大部分低效计划问题都能通过此操作解决：

UPDATE STATISTICS T_Primary;
UPDATE STATISTICS T_Secondary;

2. 添加针对性索引 #

在T_Secondary上创建覆盖过滤条件的复合索引，让原查询直接利用索引完成过滤，无需全表扫描：

CREATE NONCLUSTERED INDEX IX_T_Secondary_Path_Id ON T_Secondary(Path) INCLUDE(Id);

3. 调整原查询写法（无需临时表） #

将原查询改写为子查询形式，强制优化器优先筛选T_Secondary的数据集，引导生成更优计划：

SELECT p.Id
FROM T_Primary p
INNER JOIN (
    SELECT Id FROM T_Secondary 
    WHERE Path = @path OR Path LIKE CONCAT(@path, '\%')
) s ON s.Id = p.T_Secondary_Id
WHERE p.Probability < 1 AND p.Ignore = 0;

4. 仅在必要时使用临时表拆分 #

只有当上述方法都无法解决，且特定查询反复出现执行计划选择错误时，再考虑用临时表拆分逻辑，不要将其作为通用方案。

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