Quick Tip: Check Most DTU Expensive Queries in Azure SQL Database

Optimizing Azure SQL Database performance often begins with identifying the most resource-intensive queries. Understanding which queries consume the most DTU (Database Transaction Unit) helps prioritize tuning efforts and improve overall efficiency.

Azure SQL Database provides built-in tools to easily surface the queries with the highest DTU usage, making it straightforward for data engineers and DBAs to pinpoint performance bottlenecks.

Step-by-Step Guide

1. Connect to Your Azure SQL Database Using VSCode or SQL Server Management Studio (SSMS).
These tools allow you to run queries directly against your database.

2. Query the sys.dm_db_resource_stats DMV to Get Recent Resource Usage Data.
This dynamic management view shows DTU usage metrics aggregated for your database and is updated every 15 seconds. You can correlate this with queries identified in query stats views.
Example query to find top DTU-consuming queries:

SELECT TOP 10 
    qs.sql_handle,
    qs.plan_handle,
    total_worker_time/1000 AS total_cpu_ms,
    total_logical_reads, 
    execution_count,
    (total_worker_time / execution_count) / 1000 AS avg_cpu_ms,
    SUBSTRING(st.text, (qs.statement_start_offset/2) + 1, 
        ((CASE qs.statement_end_offset 
            WHEN -1 THEN DATALENGTH(st.text) 
            ELSE qs.statement_end_offset 
          END - qs.statement_start_offset)/2) + 1) AS query_text
FROM 
    sys.dm_exec_query_stats AS qs
CROSS APPLY 
    sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY 
    total_cpu_ms DESC;

This query highlights queries with the highest total CPU time, which mostly correlates with high DTU consumption.

3. Use Azure Portal's Query Performance Insight for Visual Analysis.
Navigate to your Azure SQL Database resource in the Azure Portal, select "Query Performance Insight," and explore the top resource-intensive queries by DTU consumption over a configurable time window.
Learn more: Query Performance Insight Documentation

4. Investigate Execution Plans for Identified Queries.
Analyze execution plans to understand why queries consume high DTUs and target specific improvements such as indexing, query rewriting, or parameter sniffing fixes.

Conclusion

By regularly identifying the most DTU expensive queries using DMV queries and Azure Query Performance Insight, you can effectively target optimization efforts on your Azure SQL Database. 

This proactive monitoring helps maintain smooth performance and cost efficiency without guesswork.

Additional tips:
- Automate these checks with scheduled scripts or alerts.
- Combine resource analysis with Azure SQL Intelligent Insights for AI-driven recommendations.