Analyzing Query Performance just got easier with SQL Azure.
With the recent update we posted to the SQL Azure service (full details of the update here), the sys.dm_exec_query_stats view is enabled. There is a ton of information about how to use the view out there but in a paragraph…
Find most frequently executed queries – (execution_count)
Find queries with most IO cycles – (total_physical_reads + total_logical_reads + total_logical_writes)
Find queries suffering most from blocking – (total_elapsed_time – total_worker_time)
Find queries with most CPU cycles – (total_worker_time)
-- here is an example with exec count SELECT TOP 100 execution_count, SUBSTRING(text,(statement_start_offset/2)+1, ((CASE statement_end_offset WHEN -1 THEN DATALENGTH(text) ELSE statement_end_offset END - statement_start_offset)/2) + 1) AS statement_text, query_plan FROM sys.dm_exec_query_stats cross apply sys.dm_exec_sql_text (sql_handle) cross apply sys.dm_exec_query_plan (plan_handle) ORDER BY execution_count DESC
We will certainly continue to invest in self supportability in future but we just took one more step further.