Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | 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.)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | 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 |
The DTU graph in the Azure Portal is plotted using the same data queried by the DMVs in the two scripts above.
Using the above queries, we learned from which metric the DTU alarm originates. Now it’s time to review criminal inquiries.
We can do this by querying the Query Store data. You can find the content about the query store on mshowto soon.
