Get average DTUs of all databases in Azure SQL Database
After reading about how to validate the DTU, I now have the following request (based on the following in the following MSDN article: https://azure.microsoft.com/en-us/documentation/articles/sql-database-upgrade-new- service-tiers / )
Select avg(unsum) as avg_DTU_percent,database_name from (
SELECT start_time,(SELECT Max(v)
FROM (VALUES (avg_cpu_percent)
, (avg_physical_data_read_percent)
, (avg_log_write_percent)
) AS value(v)) AS [unsum],database_name
FROM sys.resource_stats
WHERE database_name = '<DBNAME>')as rc GROUP BY database_name;
I want to change it to run on all databases on the server, which means I want to drop the database name and use the group by but I haven't seemed to use real sql for too long and I am missing something.
Is there a way to split the data before the inner query so I can get the data easily?
source to share
SELECT database_name
,(SELECT Max(v)
FROM (VALUES (max(avg_cpu_percent))
, (max(avg_data_io_percent))
, (max(avg_log_write_percent))
) AS value(v)) AS [max_DTU_percent]
FROM sys.resource_stats group by database_name
Will catch the highest DTU percentage.
SELECT start_time, end_time, database_name
,(SELECT Max(v)
FROM (VALUES (avg_cpu_percent)
, (avg_data_io_percent)
, (avg_log_write_percent)
) AS value(v)) AS [max_DTU_percent]
FROM sys.resource_stats
ORDER BY end_time
Gives you a list every five minutes so you can see the data range.
source to share