CODEWITHSUNDEEP
azure-sql-database
René Titulaer
René Titulaer

Reputation: 71

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

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?

enter image description here

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

enter image description here

Thanks.

Upvotes: 0

Views: 2440

Answers (1)

Alberto Morillo
Alberto Morillo

Reputation: 15668

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

Upvotes: 0

Related Questions