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

How to get a detailed CPU percentage, memory usage & Data IO percentage in Azure SQL.

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

 

 

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!