Get CPU, DataIO And Memory Usage In Azure SQL Through A Query

Introduction

If you are the owner of any Azure SQL Database, you must be using the Matrics section of the same in the Azure portal for checking the DTU, CPU, memory percentages continuously. But if you don't have the access to the Azure portal, you might not be able to check this.

Using the below query, you can directly get these numbers from the SQL server itself:

Code

CREATE SCHEMA [SystemMonitor] -- Create if not present
CREATE PROC [SystemMonitor].[GetDBStatus]
@interval INT
AS
BEGIN
DECLARE @edition varchar(50),
@serviceObjective varchar(50)
SELECT
@edition = slo.edition,
@serviceObjective =slo.service_objective
FROM sys.databases d
JOIN sys.database_service_objectives slo
ON d.database_id = slo.database_id
SELECT
AVG(avg_cpu_percent) AS 'AverageCPUPercent',
MAX(avg_cpu_percent) AS 'MaximumCPUPercent',
AVG(avg_data_io_percent) AS 'AverageDataIOPercent',
MAX(avg_data_io_percent) AS 'MaximumDataIOPercent',
AVG(avg_memory_usage_percent) AS 'AverageMemoryPercent',
MAX(avg_memory_usage_percent) AS 'MaximumMemoryPercent',
MAX(end_time) AS 'StartDatetime',
MIN(end_time) AS 'EndDatetime',
@edition AS 'Edition',
@serviceObjective 'ServiceObjective'
FROM sys.dm_db_resource_stats
WHERE end_time < GETDATE()
AND end_time >= DATEADD(minute, (-1 * @interval), getdate()) -- Set the interval as per your requirement to check
END

Result

Get CPU, DataIO And Memory Usages In Azure SQL Through Query

Now if you want to change the pricing tier from the SQL server portal, check the below link.

Change The Azure SQL Pricing Tier Using SQL Query

Enjoy!