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…

You can;

  • 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.

    Enjoy!