Checking Queries Performance on Azure SQL Database (DMVs)

In this post, I will share a couple of important queries that I am using to check performance on Azure SQL Database.

Show locking information:

SELECT
 t1.resource_type,
 t1.resource_database_id,
 t1.resource_associated_entity_id,
 t1.request_mode,
 t1.request_session_id,
 t2.blocking_session_id
FROM sys.dm_tran_locks as t1
INNER JOIN sys.dm_os_waiting_tasks as t2
ON t1.lock_owner_address = t2.resource_address;

For further details read: https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-tran-locks-transact-sql

Show Top 5 worst performance queries by average CPU Time:

SELECT TOP 5 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;

For further details read: https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-query-stats-transact-sql

Show sessions and connections details:

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;

For further details read: https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-connections-transact-sql

Return CPU intensive queries:

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;

Return queries waiting in the memory queue:

SELECT * 
FROM sys.dm_exec_query_memory_grants 
WHERE grant_time is null

Return query plan from the plan cache:

SELECT * FROM sys.dm_exec_cached_plans cp 
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle);

Return Memory Intensive Queries:

SELECT TOP 5 
total_worker_time/execution_count AS [Avg CPU Time], 
Plan_handle, query_plan  
FROM sys.dm_exec_query_stats AS qs 
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) 
ORDER BY total_worker_time/execution_count DESC;

For further details read: https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-query-memory-grants-transact-sql

Return running queries:

select * from sys.dm_exec_requests

Use the information from sql_handle to find the query statement text using the query below:

select * from sys.dm_exec_sql_text(<sql_handle>)

Return currently blocked requests:

SELECT 
session_id ,status ,blocking_session_id
,wait_type ,wait_time ,wait_resource
,transaction_id
FROM sys.dm_exec_requests
WHERE status = N'suspended';

For further details read: https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-requests-transact-sql