SQL Azure Database Activity Monitor (Using T-SQL)

This script reveals how how to implement a SQL Azure Activity Monitor utilising T-SQL.

As with everything in life, this is a good news, bad news story. The bad news is SSMS does not have Activity Monitor for SQL Azure Database and sometimes with my SQL Azure Developer hat on, I am only allowed access to the SQL Azure Database and not the Azure portal.

The good news is most of the counters are there already buried deep within the bowels of DMVs, Dynamic Management Views.

So, in next to no time I knocked together a script to emulate the Activity Monitor, I thought this would be a great start to build on this and expand it with parts on deadlocking and other such important snapshot information.

Please check out our warranty page.

-- 2nd January 2020
-- Sequallity Limited
-- SQL Azure Activity Monitor
 
SET NOCOUNT ON;
 
DECLARE @start BIGINT;
DECLARE @end BIGINT;
 
SELECT @start = cntr_value
FROM sys.dm_os_performance_counters WITH (NOLOCK)
WHERE Trim(counter_name) = 'Batch Requests/sec';
 
WAITFOR DELAY '00:00:14.998';
 
SELECT @end = cntr_value
FROM sys.dm_os_performance_counters WITH (NOLOCK)
WHERE Trim(counter_name) = 'Batch Requests/sec';
 
WITH cte_activity
AS (
	SELECT TOP 20 avg_cpu_percent AS CPU
		,avg_data_io_percent AS DiskIO
	FROM sys.dm_db_resource_stats WITH (NOLOCK)
	ORDER BY end_time DESC
	)
	,cte_blocking
AS (
	SELECT COALESCE(Count(blocking_session_id), 0) AS Blocking
	FROM sys.dm_os_waiting_tasks WITH (NOLOCK)
	)
SELECT Cast(@@servername AS VARCHAR) AS 'AzureActivityMonitorServer'
	,Cast(Avg(cpu) AS NUMERIC(5, 2)) AS CPUPct
	,Cast(Avg(diskio) AS NUMERIC(5, 2)) AS DiskIOPct
	,Max(cte_blocking.blocking) AS BlockingCnt
	,(@end - @start) * 4 AS 'BatchRequestsSecCnt'
FROM cte_activity
CROSS APPLY cte_blocking;

Leave a Reply