Monitor Azure SQL Database and Managed Instance using dynamic management views

Updated: Dec 24, 2021


To monitor the performance of your SQL database hosted in Microsoft Azure, you must have a tool that enables you to monitor it. You can monitor the database performance using dynamic management queries.


This blog will help you learn the tips and tricks to monitor the Azure SQL Database.

Microsoft Azure SQL Database gives you a way to quickly identify problems that may be causing performance issues in your databases. This is done using a set of dynamic views called Management Views. Through these views, it’s easy to see how long queries are taking and how much memory you’re using at any given moment of the day.



Calculating database size for Azure SQL Database

SELECT SUM(reserved_page_count)*8.0/1024 AS DBSize
FROM sys.dm_db_partition_stats;
GO
Get the size of individual objects (in megabytes) in Azure database:
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;
GO


Monitor database connections in Azure SQL Database

The DMV sys.dm_exec_connections view retrieves information about all of the active connections made to a specific Azure SQL Database server and observe the details of each connection. In addition, you can use sys.dm_exec_sessions to filter internal tasks and user sessions. The following query retrieves information on the current session:



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


Top queries ranked by average CPU time

SELECT TOP 10 query_stats.query_hash AS "Query Hash",
   SUM(query_stats.total_worker_time) / SUM(query_stats.execution_count) AS "Avg CPU Time",
   MIN(query_stats.statement_text) AS "Statement Text"
FROM
   (SELECT QS.*,
   SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1,
   ((CASE statement_end_offset
        WHEN -1 THEN DATALENGTH(ST.text)
        ELSE QS.statement_end_offset END
            - QS.statement_start_offset)/2) + 1) AS statement_text
    FROM sys.dm_exec_query_stats AS QS
    CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST) as query_stats
GROUP BY query_stats.query_hash
ORDER BY 2 DESC;


Get session blocking details along with blocking



SET NOCOUNT ON
GO
SELECT SPID, BLOCKED, REPLACE (REPLACE (T.TEXT, CHAR(10), ' '), CHAR (13), ' ' ) AS BATCH
INTO #T
FROM sys.sysprocesses R CROSS APPLY sys.dm_exec_sql_text(R.SQL_HANDLE) T
GO
WITH BLOCKERS (SPID, BLOCKED, LEVEL, BATCH)
AS
(
SELECT SPID,
BLOCKED,
CAST (REPLICATE ('0', 4-LEN (CAST (SPID AS VARCHAR))) + CAST (SPID AS VARCHAR) AS VARCHAR (1000)) AS LEVEL,
BATCH FROM #T R
WHERE (BLOCKED = 0 OR BLOCKED = SPID)
AND EXISTS (SELECT * FROM #T R2 WHERE R2.BLOCKED = R.SPID AND R2.BLOCKED <> R2.SPID)
UNION ALL
SELECT R.SPID,
R.BLOCKED,
CAST (BLOCKERS.LEVEL + RIGHT (CAST ((1000 + R.SPID) AS VARCHAR (100)), 4) AS VARCHAR (1000)) AS LEVEL,
R.BATCH FROM #T AS R
INNER JOIN BLOCKERS ON R.BLOCKED = BLOCKERS.SPID WHERE R.BLOCKED > 0 AND R.BLOCKED <> R.SPID
)
SELECT N'    ' + REPLICATE (N'|         ', LEN (LEVEL)/4 - 1) +
CASE WHEN (LEN(LEVEL)/4 - 1) = 0
THEN 'HEAD -  '
ELSE '|------  ' END
+ CAST (SPID AS NVARCHAR (10)) + N' ' + BATCH AS BLOCKING_TREE
FROM BLOCKERS ORDER BY LEVEL ASC
GO
DROP TABLE #T
GO

(Ref: https://techcommunity.microsoft.com/t5/azure-database-support-blog/troubleshooting-high-lock-wait-time-and-lock-time-out/ba-p/2368875)


Details about database sessions which are getting blocked:


SELECT current_timestamp as [CURRENT_TIMESTAMP]

       , DB_NAME(dtl.resource_database_id) AS database_name

       , req.session_id AS blocked_sessionID

       , ses.program_name blocked_programName

       , ses.host_name blocked_hostname

       , ses.login_name blocked_login

       , CASE ses.transaction_isolation_level

              WHEN 1 THEN 'ReadUncomitted'

              WHEN 2 THEN 'ReadCommitted'

              WHEN 3 THEN 'Repeatable'

              WHEN 4 THEN 'Serializable'

              WHEN 5 THEN 'Snapshot'

       END blocked_isolation_level

       , REPLACE(REPLACE(sqltext.TEXT, CHAR(13), ' '), CHAR(10), ' ') AS blocked_last_query

       , req.status AS [blocked_status]

       , req.command AS blocked_command

       , req.cpu_time AS blocked_cpuTime

       , req.total_elapsed_time AS blocked_totalElapsedTime

       , blocked_tran.transaction_id blocked_transaction_id

       , osw.blocking_session_id AS blocker_SessionID

       , blocker_ses.program_name blocker_programName

       , blocker_ses.host_name blocker_hostName

       , blocker_ses.login_name blocker_login

       , CASE blocker_ses.transaction_isolation_level

              WHEN 1 THEN 'ReadUncomitted'

              WHEN 2 THEN 'ReadCommitted'

              WHEN 3 THEN 'Repeatable'

              WHEN 4 THEN 'Serializable'

              WHEN 5 THEN 'Snapshot'

       END blocker_isolation_level

       , REPLACE(REPLACE(iif(blocker_sqltext.TEXT is NULL,blocker_sqltext2.event_info,blocker_sqltext.TEXT), CHAR(13), ' '), CHAR(10), ' ') AS blocker_last_query

       , blocker_req.status AS [blocker_status]

       , blocker_req.command AS blocker_command

       , blocker_req.cpu_time AS blocker_cpuTime

       , blocker_req.total_elapsed_time AS blocker_totalElapsedTime

       , blocker_proc.lastwaittype blocker_last_waittype

       , blocker_proc.last_batch blocker_last_batch

       , blocker_proc.open_tran blocker_open_tran

       , blocker_tran.transaction_id blocker_transaction_id

       , blocker_proc.cmd blocker_command

       , dtl.request_mode AS lockRequestMode

       , dtl.resource_type AS lockResourceType

       , dtl.resource_subtype AS lockResourceSubType

       , osw.wait_type AS taskWaitType

       , osw.resource_description AS taskResourceDescription

       , osw.wait_duration_ms

FROM sys.dm_exec_requests req

INNER JOIN sys.dm_exec_sessions ses on ses.session_id = req.session_id

CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) AS sqltext

INNER JOIN sys.dm_tran_locks dtl on dtl.request_session_id = req.session_id

INNER JOIN sys.dm_os_waiting_tasks osw on osw.session_id = req.session_id

LEFT JOIN sys.dm_tran_session_transactions blocked_tran on blocked_tran.session_id =req.session_id

INNER JOIN dbo.sysprocesses blocker_proc on osw.blocking_session_id = blocker_proc.spid

LEFT JOIN sys.dm_exec_requests blocker_req on blocker_req.session_id = osw.blocking_session_id

LEFT JOIN sys.dm_exec_sessions blocker_ses on blocker_ses.session_id = osw.blocking_session_id

LEFT JOIN sys.dm_tran_session_transactions blocker_tran on blocker_tran.session_id =osw.blocking_session_id

OUTER APPLY sys.dm_exec_sql_text(blocker_req.sql_handle) AS blocker_sqltext

OUTER APPLY sys.dm_exec_input_buffer(osw.blocking_session_id,0) as blocker_sqltext2;


Monitoring query plans in Azure SQL Database


SELECT
   highest_cpu_queries.plan_handle,
   highest_cpu_queries.total_worker_time,
   q.dbid,
   q.objectid,
   q.number,
   q.encrypted,
   q.[text]
FROM
   (SELECT TOP 50
        qs.plan_handle,
        qs.total_worker_time
   FROM
        sys.dm_exec_query_stats qs
   ORDER BY qs.total_worker_time desc) AS highest_cpu_queries
   CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS q
ORDER BY highest_cpu_queries.total_worker_time DESC;




43 views0 comments