Azure SQL Database 100% DTU while query DTU usage is low

Question

while running a load test the DTU of Azure SQL Database goes to 100%. The next step would be to have a look which query is causing this but the queries have low DTU usage. This can be seen in the picture below.

How to find out what is causing the high DTU usage?

I did run the query as suggested by Alberto, below the result. CPU is clearly the bottleneck.

Thanks.

Alberto Morillo Alberto Morillo · Accepted Answer · 2018-12-12T15:49:50

The following query tells you which resource consumption is creating DTU spike:

SELECT 
    (COUNT(end_time) - SUM(CASE WHEN avg_cpu_percent > 80 THEN 1 ELSE 0 END) * 1.0) / COUNT(end_time) AS 'CPU Fit Percent'
    ,(COUNT(end_time) - SUM(CASE WHEN avg_log_write_percent > 80 THEN 1 ELSE 0 END) * 1.0) / COUNT(end_time) AS 'Log Write Fit Percent'
    ,(COUNT(end_time) - SUM(CASE WHEN avg_data_io_percent > 80 THEN 1 ELSE 0 END) * 1.0) / COUNT(end_time) AS 'Physical Data Read Fit Percent'
FROM sys.dm_db_resource_stats

Azure SQL Database 100% DTU while query DTU usage is low

1 Answers