如何识别Azure Premium P2数据库中消耗DTU最多的查询？

针对你遇到的Premium P2数据库DTU居高不下，但性能概览未发现明显问题的情况，可以通过以下步骤逐步定位原因：

• 实时排查当前运行的高消耗会话
  由于sys.dm_db_resource_stats是5分钟粒度的聚合数据，可能无法捕捉瞬时高消耗的任务，用以下查询查看当前正在运行的会话，按CPU消耗排序：

  SELECT 
      r.session_id,
      r.status,
      r.command,
      r.cpu_time,
      r.total_elapsed_time,
      r.reads,
      r.writes,
      r.logical_reads,
      s.text AS query_text
  FROM sys.dm_exec_requests r
  CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s
  WHERE r.session_id <> @@SPID
  ORDER BY r.cpu_time DESC;
  

  重点关注cpu_time、logical_reads数值高的会话，对应的query_text就是当前消耗资源的语句。

• 分析历史高消耗查询
  查看历史执行的查询中，总CPU、IO消耗最高的语句，即使这些查询当前未运行，也可能是高频或单次消耗过大导致DTU持续高位：

  SELECT 
      TOP 20
      SUBSTRING(s.text, (qs.statement_start_offset/2)+1, 
          ((CASE qs.statement_end_offset 
            WHEN -1 THEN DATALENGTH(s.text) 
            ELSE qs.statement_end_offset 
           END - qs.statement_start_offset)/2)+1) AS query_text,
      qs.total_worker_time AS total_cpu_time,
      qs.total_worker_time/qs.execution_count AS avg_cpu_time,
      qs.total_elapsed_time/qs.execution_count AS avg_duration,
      qs.total_logical_reads/qs.execution_count AS avg_logical_reads,
      qs.execution_count
  FROM sys.dm_exec_query_stats qs
  CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) s
  ORDER BY qs.total_worker_time DESC;
  

  通过total_cpu_time和execution_count判断是单次高消耗还是高频低消耗的查询导致的资源占用。

• 通过等待类型定位瓶颈类型
  数据库的等待类型能直接反映资源瓶颈的本质，用以下查询过滤掉无意义的等待类型，聚焦关键等待：

  SELECT 
      wait_type,
      wait_time_ms,
      signal_wait_time_ms,
      wait_time_ms - signal_wait_time_ms AS resource_wait_time_ms,
      waiting_tasks_count
  FROM sys.dm_db_wait_stats
  WHERE wait_type NOT IN ('SLEEP_TASK', 'WAITFOR', 'LOGMGR_QUEUE', 'CHECKPOINT_QUEUE', 'LAZYWRITER_SLEEP')
  ORDER BY wait_time_ms DESC;
  

  例如：

  • PAGEIOLATCH_*类等待说明数据IO是瓶颈；
  • CXPACKET等待可能是并行查询的协调问题；
  • RESOURCE_SEMAPHORE等待表示内存资源不足；
  • WRITELOG等待说明日志写入压力大。

• 检查后台系统维护任务
  自动索引重建、统计信息更新、数据库备份等后台任务也会占用大量DTU，尤其是在业务高峰期运行时：

  SELECT 
      session_id,
      command,
      cpu_time,
      reads,
      writes,
      total_elapsed_time,
      text AS query_text
  FROM sys.dm_exec_requests
  WHERE command IN ('ALTER INDEX', 'UPDATE STATISTICS', 'BACKUP DATABASE')
  ORDER BY cpu_time DESC;
  

  如果发现这类任务在高峰时段运行，建议调整维护窗口到业务低峰期。

• 细化DTU瓶颈指标
  DTU是CPU、数据IO、日志写入三个指标的最大值，用以下查询查看每个时间点哪个指标是DTU的主要消耗项：

  SELECT 
      end_time,
      avg_cpu_percent,
      avg_data_io_percent,
      avg_log_write_percent,
      avg_memory_usage_percent,
      dtu_limit,
      (SELECT MAX(v) FROM (VALUES (avg_cpu_percent), (avg_data_io_percent), (avg_log_write_percent)) AS value(v)) AS max_dtu_used
  FROM sys.dm_db_resource_stats
  ORDER BY end_time DESC;
  

  找到持续接近100%的指标，就能确定DTU居高不下的核心原因。

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