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 1 SUBSTRING(s2.text,statement_start_offset / 2+1 ,
((CASE WHEN statement_end_offset = –1
(LEN(CONVERT(nvarchar(max),s2.text)) * 2)
– statement_start_offset) / 2+1)) AS sql_statement,
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.