DTU Problem For Azure SQL Database

Çağlar Özenç

2 years ago

In today’s article we will talk about DTU Problem For Azure SQL Database.

If you are using Azure SQL database, you may have encountered a concept called DTU (Database Transaction Unit). DTU is a blended measure of CPU, Data, IO, Log Writes and memory.

If you receive a warning that the database is consuming high DTUs, the first step to investigate the cause would be to find out which value of the DTU actually went up ( CPU, Data,IO etc ).

You can see the average of the DTU values of the last 14 days using the query below.

The start and end times in the printout are in UTC. You need to pay attention to this.

SELECT
[start_time]
,[end_time]
,[database_name]
,[avg_cpu_percent]
,[avg_data_io_percent]
,[avg_log_write_percent]
,(
SELECT Max(v)
FROM (
VALUES ([avg_cpu_percent])
,([avg_data_io_percent])
,([avg_log_write_percent])
) AS value(v)
) AS [avg_DTU_percent]
FROM sys.resource_stats
–WHERE [database_name] = ‘DB Name Here’
ORDER BY start_time desc

SELECT

[start_time]

,[end_time]

,[database_name]

,[avg_cpu_percent]

,[avg_data_io_percent]

,[avg_log_write_percent]

SELECT Max(v)

FROM (

VALUES ([avg_cpu_percent])

,([avg_data_io_percent])

,([avg_log_write_percent])

) AS value(v)

) AS [avg_DTU_percent]

FROM sys.resource_stats

–WHERE [database_name] = ‘DB Name Here’

ORDER BY start_time desc

If the DTU alarm has now occurred, you can run the following query for the affected database. (It gives the data collected every 15 seconds.)

SELECT
end_time
,[avg_cpu_percent]
,[avg_data_io_percent]
,[avg_log_write_percent]
,(
SELECT Max(v)
FROM (
VALUES ([avg_cpu_percent])
,([avg_data_io_percent])
,([avg_log_write_percent])
) AS value(v)
) AS [avg_DTU_percent]
FROM sys.dm_db_resource_stats

SELECT

end_time