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;
GOMonitor 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_idTop 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
GODetails 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;