Azure SQL Server未知定期SQL查询致DTU突增，求排查方案

问题背景 #

生产环境Azure SQL Server作为供约50名用户使用的控制台应用数据存储，每4小时会出现一次DTU资源使用率突增情况。

已完成的排查动作 #

• 通过以下动态管理视图定位到资源消耗最高的查询：

  sys.dm_exec_query_stats, sys.dm_exec_cached_plans, sys.dm_exec_sql_text
  

• 发现两条针对数据库中每张表定期执行的查询（用于计算各列平均占用字节数）：
  第一条查询：

  SELECT TOP 131072 
      ISNULL(AVG(CAST(ISNULL(DATALENGTH([COLUMN_1]), 0) AS FLOAT)), 0),
      ISNULL(AVG(CAST(ISNULL(DATALENGTH([COLUMN_2]), 0) AS FLOAT)), 0),
      ISNULL(AVG(CAST(ISNULL(DATALENGTH([COLUMN_3]), 0) AS FLOAT)), 0),
      ISNULL(AVG(CAST(ISNULL(DATALENGTH([COLUMN_N]), 0) AS FLOAT)), 0)
  FROM [dbo].[MY_TABLE] 
  TABLESAMPLE (100 percent)
  

  第二条查询：

  SELECT TOP 100 
      ISNULL(AVG(CAST(ISNULL(DATALENGTH([COLUMN_1]), 0) AS FLOAT)), 0),
      ISNULL(AVG(CAST(ISNULL(DATALENGTH([COLUMN_2]), 0) AS FLOAT)), 0),
      ISNULL(AVG(CAST(ISNULL(DATALENGTH([COLUMN_3]), 0) AS FLOAT)), 0),
      ISNULL(AVG(CAST(ISNULL(DATALENGTH([COLUMN_N]), 0) AS FLOAT)), 0)
  FROM [dbo].[MY_TABLE]
  

  关键特征：第一条查询使用131072（即2^17）和TABLESAMPLE (100 percent)；两条查询针对每张表的执行次数完全一致。
• 已尝试的定位方法：
  • 在代码库和网络中搜索查询特征字符串（如"131072"、"TABLESAMPLE"、"ISNULL(AVG(CAST(ISNULL(DATALENGTH"），无结果。
  • 确认Azure数据库自动性能调优功能未启用。
  • 排查Entity Framework框架，未找到相关查询生成逻辑。
• 用于定位高消耗查询的语句：

  SELECT TOP 25 
              DB_NAME(st.dbid) DatabaseName,
              SUBSTRING(text, 
              CASE WHEN statement_start_offset = 0 OR statement_start_offset IS NULL  THEN 1  ELSE statement_start_offset/2 + 1 END, 
              CASE WHEN statement_end_offset = 0 OR statement_end_offset = -1   OR statement_end_offset IS NULL  THEN LEN(text)  ELSE statement_end_offset/2 END - 
              CASE WHEN statement_start_offset = 0  OR statement_start_offset IS NULL THEN 1   ELSE statement_start_offset/2  END + 1)  AS [Query],
              cp.usecounts AS [ExecCount],
              CAST(qs.total_elapsed_time / (1000000.00 * usecounts) AS DECIMAL(18,2)) AverageDuration,
              qs.last_execution_time LastExec,
              DATEDIFF(SECOND, qs.last_execution_time, getdate()) AS LastExecSecsAgo,
              qs.total_worker_time    AS CpuTime,
              qs.total_elapsed_time   AS ElapsedTime,
              qs.total_logical_reads  AS LogicalReads,
              qs.total_logical_writes AS LogicalWrites,
              qs.total_physical_reads AS PhysicalReads
  FROM        sys.dm_exec_query_stats qs  
  JOIN        sys.dm_exec_cached_plans cp on qs.plan_handle = cp.plan_handle 
  CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
  ORDER BY    qs.total_elapsed_time DESC,
              qs.total_worker_time DESC, 
              qs.total_logical_reads DESC
  OPTION (RECOMPILE); 
  

排查思路建议 #

一、定位查询执行来源 #

1. 扩展查询捕获执行上下文
   给现有定位语句加入会话和连接信息，获取执行查询的机器、账号、应用程序名称：

   SELECT TOP 25 
       DB_NAME(st.dbid) DatabaseName,
       SUBSTRING(text, 
           CASE WHEN statement_start_offset = 0 OR statement_start_offset IS NULL THEN 1 ELSE statement_start_offset/2 + 1 END, 
           CASE WHEN statement_end_offset = 0 OR statement_end_offset = -1 OR statement_end_offset IS NULL THEN LEN(text) ELSE statement_end_offset/2 END - 
           CASE WHEN statement_start_offset = 0 OR statement_start_offset IS NULL THEN 1 ELSE statement_start_offset/2 END + 1) AS [Query],
       cp.usecounts AS [ExecCount],
       CAST(qs.total_elapsed_time / (1000000.00 * usecounts) AS DECIMAL(18,2)) AverageDuration,
       qs.last_execution_time LastExec,
       DATEDIFF(SECOND, qs.last_execution_time, GETDATE()) AS LastExecSecsAgo,
       qs.total_worker_time AS CpuTime,
       qs.total_elapsed_time AS ElapsedTime,
       qs.total_logical_reads AS LogicalReads,
       qs.total_logical_writes AS LogicalWrites,
       qs.total_physical_reads AS PhysicalReads,
       -- 新增上下文信息
       s.session_id,
       s.login_name,
       s.host_name,
       s.program_name,
       s.client_interface_name
   FROM sys.dm_exec_query_stats qs  
   JOIN sys.dm_exec_cached_plans cp ON qs.plan_handle = cp.plan_handle 
   CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
   JOIN sys.dm_exec_sessions s ON qs.session_id = s.session_id
   ORDER BY qs.total_elapsed_time DESC, qs.total_worker_time DESC, qs.total_logical_reads DESC
   OPTION (RECOMPILE);
   

   通过program_name（第三方工具/监控脚本）、host_name（执行机器）、login_name（执行账号）缩小来源范围。

2. 用Extended Events捕获实时执行
   在Azure SQL中创建Extended Events会话，过滤目标查询并捕获详细上下文：

   CREATE EVENT SESSION [CaptureUnknownQueries] ON DATABASE 
   ADD EVENT sqlserver.sql_statement_completed(
       ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.login_name,sqlserver.sql_text)
       WHERE (sqlserver.sql_text LIKE '%TABLESAMPLE%' OR sqlserver.sql_text LIKE '%131072%'))
   ADD TARGET package0.event_file(SET filename=N'CaptureUnknownQueries')
   WITH (STARTUP_STATE=OFF);
   

   启动会话，等下一次DTU突增后停止，分析捕获的事件获取来源信息。

3. 排查第三方工具与服务

   • 检查数据库监控工具（如Azure Monitor自定义脚本、第三方性能监控工具）是否在定期执行表统计分析。
   • 排查ETL工具、数据同步服务是否在扫描表结构或统计信息。
   • 确认备份、索引重建、统计信息更新等维护任务是否附带此类查询。

4. 深度排查Entity Framework相关逻辑

   • 检查Database.SqlQuery或原生SQL调用，排查封装在类库、NuGet包中的隐藏逻辑。
   • 检查动态查询生成器、T4模板等代码生成工具，是否在后台生成统计类查询。
   • 排查依赖的报表工具、ORM扩展库，是否存在自动收集表列信息的功能。

二、临时缓解资源压力 #

1. 优化查询逻辑

   • 第一条查询中TABLESAMPLE (100 percent)完全冗余，会额外增加全表扫描开销，若能控制来源可直接删除该语句。
   • 改用系统视图获取近似统计值，避免全表扫描：

     SELECT 
         AVG(CAST(DATALENGTH(c.name) AS FLOAT)) AS AvgColumnLength
     FROM sys.columns c
     JOIN sys.tables t ON c.object_id = t.object_id
     JOIN sys.schemas s ON t.schema_id = s.schema_id
     WHERE s.name = 'dbo' AND t.name = 'MY_TABLE'
     

2. 资源隔离与调度调整

   • 用Azure SQL的资源治理功能（如工作负载隔离）限制此类查询的CPU/IO消耗。
   • 若找到来源，调整任务调度时间避开业务高峰。

三、长期预防措施 #

1. 启用数据库审计
   开启Azure SQL审计功能，记录所有查询的执行来源，便于后续快速定位未知查询。
2. 定期监控计划缓存
   定期检查缓存的查询计划，及时发现异常或高消耗的未知查询。
3. 全依赖扫描
   用静态代码分析工具扫描所有依赖的NuGet包、类库，排查生成此类查询的隐藏逻辑。

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