Analyzing Query Performance just got easier with SQL Azure.

  • Article

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!