SQL Server查询自阻塞（SELECT(STATMAN)）：原因与解决方法

原始查询代码 #

;WITH --first CTE is your data set example
    CTE
    AS
    (
        SELECT *
        FROM (VALUES
                ('2023-04-06', 0029, 'D', 'ABCD', 1, 100),
                ('2023-04-06', 0027, 'D', 'ABCD', 1, 200),
                ('2023-04-06', 0044, 'D', 'ABCD', 1, 300),
                ('2023-04-06', 0042, 'D', 'ABCD', 1, 400),
                ('2023-04-06', 0029, 'C', 'ABCD', 1, 500),
                ('2023-04-06', 0069, 'C', 'ABCD', 1, 600),
                ('2023-04-06', 0067, 'C', 'XXCD', 1, 700),
                ('2023-04-06', 0089, 'C', 'ABCD', 1, 800),
                ('2023-04-06', 0079, 'C', 'XXCD', 1, 900),
                ('2023-04-06', 0084, 'C', 'ABCD', 1, 1000)) AS T([BOOKING_DATE],[TIME_INTERVAL],[DB_CR_CODE],[CHANNEL],[NBR_OF_TXN],[AMOUNT])
    ),
    CTE2 --aggregate data 
    AS
    (
        SELECT
            booking_date, interval, product_group1, product_group2,
            SUM(nbr_of_txn) AS nbr_txn, SUM(amount) AS amount
        FROM
            (SELECT
                BOOKING_DATE,
                CASE
             WHEN TIME_INTERVAL BETWEEN 0000 AND 0030 THEN 1
             WHEN TIME_INTERVAL BETWEEN 0031 AND 0060 THEN 2
             WHEN TIME_INTERVAL BETWEEN 0061 AND 0090 THEN 3
             ELSE 99 
         END AS interval,
                CASE
             WHEN DB_CR_CODE = 'C' THEN 'Credit'
             WHEN DB_CR_CODE = 'D' THEN 'Debit'        
             ELSE '' 
         END AS PRODUCT_GROUP1,
                CASE 
             WHEN DB_CR_CODE = 'C' AND CHANNEL = 'ABCD' THEN 'Credit_ABCD'  
             ELSE '' 
         END AS PRODUCT_GROUP2,
                NBR_OF_TXN, AMOUNT
            FROM
                CTE) a
        GROUP BY booking_date, interval, PRODUCT_GROUP1, PRODUCT_GROUP2
    )

    --UNION from CTE2
    SELECT booking_date, interval, product_group1, nbr_txn, amount
    FROM CTE2
    WHERE product_group1 !=''
    UNION ALL
    SELECT booking_date, interval, product_group2, nbr_txn, amount
    FROM CTE2
    WHERE product_group2 !=''

问题解答 #

1. SELECT(STATMAN)是什么？ #

SELECT(STATMAN)是SQL Server内部用于收集或更新统计信息的系统操作。统计信息是查询优化器生成高效执行计划的核心依据，STATMAN进程负责扫描表或索引数据，计算列的取值范围、频率分布等统计值，它并非用户发起的查询，而是SQL Server自动触发的后台操作。

2. 仅执行读取操作为何会出现该情况？ #

默认情况下SQL Server的AUTO_UPDATE_STATISTICS选项为ON，当查询访问的表数据变更量超过阈值（默认是数据量的20%+500行，大表可调整），统计信息会被标记为过期。此时执行查询时，优化器会自动触发STATMAN进程更新统计信息。

对于处理10-15百万条记录的大表，统计更新需要扫描大量数据，这个过程会与你的查询争夺锁资源、IO资源，进而形成自阻塞——你的查询等待STATMAN完成统计更新，而STATMAN可能因查询占用的资源无法快速完成。此外，部分复杂查询在生成执行计划前，优化器也可能临时调用STATMAN收集额外统计信息，触发此类阻塞。

3. 解决方法 #

• 手动预更新统计信息：在每日查询执行前，手动更新相关表的统计信息，避免查询过程中自动触发更新：

  UPDATE STATISTICS [你的表名] WITH FULLSCAN;
  

  FULLSCAN会扫描全表生成精准统计，适合大表场景。

• 启用异步统计更新：将数据库的AUTO_UPDATE_STATISTICS_ASYNC设为ON，让统计更新在后台异步执行，不阻塞当前查询：

  ALTER DATABASE [你的数据库名] SET AUTO_UPDATE_STATISTICS_ASYNC ON;
  

• 调整统计更新阈值：对于超大规模表，默认20%的变更阈值过高，可启用Trace Flag 2371，让SQL Server根据表大小动态调整阈值（表越大，阈值百分比越低）：

  DBCC TRACEON(2371, -1);
  

  该参数需重启SQL Server后生效，也可通过启动参数永久开启。

• 优化查询逻辑：

  • 用UNPIVOT替代当前的UNION ALL逻辑，减少对CTE2的重复扫描：

    SELECT booking_date, interval, product_group, nbr_txn, amount
    FROM CTE2
    UNPIVOT (
        product_group FOR groups IN (product_group1, product_group2)
    ) AS unpvt
    WHERE product_group != '';
    

  • 避免嵌套子查询，将CASE逻辑直接整合到聚合查询中，减少中间结果集的生成。

• 添加覆盖索引：针对查询中用到的过滤、分组字段（BOOKING_DATE, TIME_INTERVAL, DB_CR_CODE, CHANNEL）创建覆盖索引，包含聚合所需的NBR_OF_TXN和AMOUNT，避免全表扫描：

  CREATE NONCLUSTERED INDEX IX_YourTable_BookingDate_Interval_Code_Channel
  ON [你的表名] (BOOKING_DATE, TIME_INTERVAL, DB_CR_CODE, CHANNEL)
  INCLUDE (NBR_OF_TXN, AMOUNT);
  

• 优化视图设计：若最终要嵌入视图，复杂的CTE和聚合逻辑会导致优化器难以生成高效计划，可考虑：

  • 将聚合逻辑提前到ETL过程，预先计算每日的聚合结果存储到中间表，视图直接读取中间表。
  • 创建索引视图，将聚合结果持久化，大幅提升查询性能（需包含COUNT_BIG(*)字段）。

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