How to Measure Resource Status on Azure? – Interview Question of the Week #264

Question: How to Measure Resource Status on Azure?

Answer: While I work on Comprehensive Database Performance Health Check, I get to work on on-premises SQL Server deployments as well as Microsoft Azure. Every single time when I start working with Azure, I always want to know what is the status of Azure resource status or consumption in the last hour. Here is a quick script that can help us to measure resource status on Azure for the last hour.

How to Measure Resource Status on Azure? - Interview Question of the Week #264 resource-status-800x260

AVG(avg_cpu_percent) AS 'Average CPU %',
MAX(avg_cpu_percent) AS 'Maximum CPU %',
AVG(avg_data_io_percent) AS 'Average data IO %',
MAX(avg_data_io_percent) AS 'Maximum data IO %',
AVG(avg_log_write_percent) AS 'Average log write %',
MAX(avg_log_write_percent) AS 'Maximum log write %',
AVG(avg_memory_usage_percent) AS 'Average memory %',
MAX(avg_memory_usage_percent) AS 'Maximum memory %'
FROM sys.dm_db_resource_stats; 

When you run the above query, it will give you the necessary feedback about how your resource consumption is going on at the moment as well in the last 60 minutes. The DMV dm_db_resource_stats contains one row for every 15 seconds, even if there is no activity in the database. The view manages historical data is maintained for approximately one hour. Please note that if the database was failed over to another server within the last 60 minutes, the view will only return data for the time it has been the primary database since that failover.

NOTE: The script in this blog will only work on Azure.

Here are a few additional interview questions which you may find interesting.

If you are using Azure and have similar scripts that can help users with performance optimization. Do post them in the comments section and I will post it with due credit to you.

Reference: Pinal Dave (

SQL Azure, SQL Performance, SQL Scripts, SQL Server
Previous Post
How to Measure Transactions Per Seconds in SQL Server? – Interview Question of the Week #262
Next Post
How to Forecast the Size of Restore of Backup in SQL Server? – Interview Question of the Week #265

Related Posts

1 Comment. Leave new

  • Francesco Mantovani
    September 27, 2022 3:14 pm

    Any way we could monitor performances for more than 1h?
    Can you confirm that Kusto queries is the only alternative?


Leave a Reply