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.

SELECT
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.

• How to Measure Transactions Per Seconds in SQL Server? – Interview Question of the Week #262
• What are Different Methods to Know the Row Count of Table? – Interview Question of the Week #261
• How to Recompile Stored Procedure? – Interview Question of the Week #260
• What is TempDB Spill in SQL Server? – Interview Question of the Week #259
• How to Map Network Drive as Fixed Drive? – Interview Question of the Week #258
• How to Copy Files in SQL Server? – Interview Question of the Week #257
• Can Admin Rename SA Account in SQL Server? – Interview Question of the Week #256

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 (https://blog.sqlauthority.com)