How to get the data of DTU utilization from T-SQL which is same as the values shown in azure portal?

Question

Hi everyone,

As you know, we can get the metrics of DTU percentage in azure portal's metrics section.

But how can we get the data from Azure SQL database by T-SQL?

From below link, it says we can use the dmv to get the data, but the value by the dmv is not the same with the values in azure portal.

https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-resource-stats-azure-sql-database?view=azuresqldb-current

Answer 1

Hi
WisonHii •,

Thanks for posting this question in Microsoft Q&A forum.

As we understand, you want to know value difference between DTU over portal and with DMVs.

The data returned by sys.dm_db_resource_stats is expressed as a percentage of the maximum allowed limits for the service tier/performance level that you are running.

If the database was failed over to another server within the last 60 minutes, the view will only return data for the time since that failover.

For a less granular view of this data with longer retention period, use the sys.resource_stats catalog view in Azure SQL Database, or the sys.server_resource_stats catalog view in Azure SQL Managed Instance. This view captures data every 5 minutes and maintains historical data for 14 days. For more information, see sys.resource_stats or sys.server_resource_stats.

The DTU limit of a database is determined by CPU, reads, writes, and memory available to the database. However, because the SQL Database engine typically uses all available memory for its data cache to improve performance, the avg_memory_usage_percent value will usually be close to 100 percent, regardless of current database load. Therefore, even though memory does indirectly influence the DTU limit, it is not used in the DTU utilization formula.

Reference: DTU Model

https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-resource-stats-azure-sql-database?view=azuresqldb-current

Hope this helps.

Please share the differences noticed by you in the form of screenshots so that we can look into it and assist further.

Thank you.

Answer 2

A database transaction unit (DTU) represents a blended measure of CPU, memory, reads, and writes. We don't have a Dynamic Management View that can tell us what queries, sessions or tasks are consuming more DTU. However, you can use below query to see what metric is over 85% or 90% consistently.

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; 

Then you can use DMVs or Query Store to identify queries using more CPU, IO, or memory depending on what you see are the results of above query.