Find out which query increasing DTU in SQL Azure

Question

I am using SQL Azure SQL Server for my App. The DB tier, which my app was in was working perfectly till recently and the avg DTU usage was under control. But off late, during peak hours, the DTU spikes to touch 100% consistently. Upgrading to the Next tier is an option, but first i want to figure out which query is responsible for this. Is this possible to find in Azure, which query made the DTU jump to 100%?

Answer 1

Simply put DTU is a blend of CPU,IO,Memory you will get based on your service tier..It would be really great if there is column which shows how much DTU the query used compared to total DTU..

I would deal this issue in two steps..

Step1:
Find out Which metric is consistently more than 90%

--This DMV stores DTU usage for every 15 seconds upto 14 days..

SELECT  
    AVG(avg_cpu_percent) AS 'Average CPU Utilization In Percent', 
    MAX(avg_cpu_percent) AS 'Maximum CPU Utilization In Percent', 
    AVG(avg_data_io_percent) AS 'Average Data IO In Percent', 
    MAX(avg_data_io_percent) AS 'Maximum Data IO In Percent', 
    AVG(avg_log_write_percent) AS 'Average Log Write Utilization In Percent', 
    MAX(avg_log_write_percent) AS 'Maximum Log Write Utilization In Percent', 
    AVG(avg_memory_usage_percent) AS 'Average Memory Usage In Percent', 
    MAX(avg_memory_usage_percent) AS 'Maximum Memory Usage In Percent' 
FROM sys.dm_db_resource_stats; 

Step2:
If you see any of the above metric consistently more than 90%,you can tune those queries..

For Example,if cpu is more than 90% ,you can start tracking the queries which are having high cpu usage and tune them..

Updated as of 20171701:
SQLAzure introduced Query Performance insight,which shows DTU used by a Query.You will have to enable Querystore for this to work..

As you can see from screenshot above,you can see exact DTU usage for each query