Today I started writing a service whose goal is to monitor my Windows Azure SQL Database in order to spot problems as soon as possible. Going through the Dynamic Management Views and Functions (Transact-SQL) documentation I came up with the following query to a list the 20 queries that consume the most Average CPU Time.
SELECT TOP 20 total_worker_time/execution_count AS [avg_cpu_time],
(SELECT TOP 1 SUBSTRING(s2.text,statement_start_offset / 2+1 ,
((CASE WHEN statement_end_offset = –1
THEN
(LEN(CONVERT(nvarchar(max),s2.text)) * 2)
ELSE
statement_end_offset
END)
– statement_start_offset) / 2+1)) AS sql_statement,
execution_count,
plan_generation_num,
last_execution_time,
total_worker_time,
last_worker_time,
min_worker_time,
max_worker_time,
total_physical_reads,
last_physical_reads,
min_physical_reads,
max_physical_reads,
total_logical_writes,
last_logical_writes,
min_logical_writes,
max_logical_writes,
s1.sql_handle
FROM sys.dm_exec_query_stats AS s1
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2
WHERE s2.objectid is null
ORDER BY (total_worker_time/execution_count) DESC;
(SELECT TOP 1 SUBSTRING(s2.text,statement_start_offset / 2+1 ,
((CASE WHEN statement_end_offset = –1
THEN
(LEN(CONVERT(nvarchar(max),s2.text)) * 2)
ELSE
statement_end_offset
END)
– statement_start_offset) / 2+1)) AS sql_statement,
execution_count,
plan_generation_num,
last_execution_time,
total_worker_time,
last_worker_time,
min_worker_time,
max_worker_time,
total_physical_reads,
last_physical_reads,
min_physical_reads,
max_physical_reads,
total_logical_writes,
last_logical_writes,
min_logical_writes,
max_logical_writes,
s1.sql_handle
FROM sys.dm_exec_query_stats AS s1
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2
WHERE s2.objectid is null
ORDER BY (total_worker_time/execution_count) DESC;
Using the results from this query observe the impact that our new code has on the overall database performance. The service sends out email notifications to the development team if the average execution time of queries is greater than a predefined threshold. Consequently, the team is able to tackle performance issues early in the development life cycle.
Alexandre Brisebois ☁ 
Hello,
Is it possible to add username or device that are running the queries?
Scenario is that we have 1 DB where multiple systems are connecting and running queries and we want to understand what system is performing the “bad” one.
LikeLike