CODEWITHSUNDEEP
sql-servert-sqlazureazure-sql-database
Srgrn
Srgrn

Reputation: 1825

Getting the average DTUs of all databases on Azure SQL Database

After reading about how to check DTUs, I now have the following query (based on the one 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 modify it to run on all databases in the server which means I want to remove the where database_name and use group by but I seem to have not used real sql for too long and am missing something

Is there a way to partition the data before the internal query so i can get the data easily?

Upvotes: 5

Views: 977

Answers (1)

cbattlegear
cbattlegear

Reputation: 854

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

Would grab the highest DTU percentage recorded.

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

Will give you a list for every five minutes so you can see the range of data.

Upvotes: 6

Related Questions