Data Platform Tips 46 - Useful DMVs to monitor Azure SQL Database

Azure SQL Database provides a set of Dynamic Management Views (DMVs) to manage and diagnose problems. Most of the common problems with Azure SQL Database can be easily detected by DMVs.

Before you can start executing the DMVs make sure the VIEW DATABASE PERMISSION is set for the Database user.

GRANT VIEW DATABASE STATE TO database_user;

Top 10 Queries consuming High CPU

SELECT TOP 10 Getdate() runtime,
     * 
FROM   (SELECT query_stats.query_hash, 
               Sum(query_stats.cpu_time)    'Total_Request_Cpu_Time_Ms', 
               Sum(logical_reads)           'Total_Request_Logical_Reads', 
               Min(start_time)              'Earliest_Request_start_Time', 
               Count(*)                     'Number_Of_Requests', 
               Substring(Replace(Replace(Min(query_stats.statement_text), Char( 
                                 10), ' ' 
                                 ), Char( 
                         13), ' '), 1, 256) AS "Statement_Text" 
        FROM   (SELECT req.*, 
                       Substring(ST.text, ( req.statement_start_offset / 2 ) + 1 
                       , ( ( 
                       CASE statement_end_offset 
                       WHEN -1 THEN Datalength(ST.text) 
                       ELSE req.statement_end_offset 
                       END - req.statement_start_offset ) / 2 ) + 1) AS 
                       statement_text 
                FROM   sys.dm_exec_requests AS req 
                       CROSS apply sys.Dm_exec_sql_text(req.sql_handle) AS ST) 
               AS 
               query_stats 
        GROUP  BY query_hash) AS t 
ORDER  BY total_request_cpu_time_ms DESC;

Top 10 long running queries

SELECT TOP 10 req.session_id,   
              req.start_time, 
              cpu_time                                    'cpu_time_ms', 
              Object_name(ST.objectid, ST.dbid)           'ObjectName', 
              Substring(Replace(Replace(Substring(ST.text, 
                                        ( req.statement_start_offset / 2 ) 
                                        + 1, ( ( 
                                                  CASE 
                                                  statement_end_offset 
                                                  WHEN -1 THEN 
                                                  Datalength(ST.text) 
                                                  ELSE 
                                                  req.statement_end_offset 
                                        END - req.statement_start_offset ) / 2 ) 
                                             + 1), Char(10 
                                ), ' '), 
                                  Char(13), ' '), 1, 512) AS statement_text 
FROM   sys.dm_exec_requests AS req 
       CROSS apply sys.Dm_exec_sql_text(req.sql_handle) AS ST 
ORDER  BY cpu_time DESC; 

Monitoring Connections

SELECT
    c.session_id, c.net_transport, c.encrypt_option,
    c.auth_scheme, s.host_name, s.program_name,
    s.client_interface_name, s.login_name, s.nt_domain,
    s.nt_user_name, s.original_login_name, c.connect_time,
    s.login_time
FROM sys.dm_exec_connections AS c
JOIN sys.dm_exec_sessions AS s
    ON c.session_id = s.session_id
WHERE c.session_id = @@SPID;

Retrieve Database Size

SELECT SUM(CAST(FILEPROPERTY(name, 'SpaceUsed') AS bigint) * 8192.) / 1024 / 1024 
AS DatabaseSizeInMB
FROM sys.database_files
WHERE type_desc = 'ROWS';

Individual Object Sizes

SELECT sys.objects.name, SUM(reserved_page_count) * 8.0 / 1024
FROM sys.dm_db_partition_stats, sys.objects
WHERE sys.dm_db_partition_stats.object_id = sys.objects.object_id
GROUP BY sys.objects.name;

Few more other queries you can use to monitor the performance of your Azure SQL Database. https://docs.microsoft.com/en-us/azure/sql-database/sql-database-monitoring-with-dmvs