Identifying the Top 20 Slowest Queries Being Executed Against a Windows Azure SQL Database

June 28, 2013 — 2 Comments


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;

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.

2 responses to Identifying the Top 20 Slowest Queries Being Executed Against a Windows Azure SQL Database

  1. 

    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.

    Like

Trackbacks and Pingbacks:

  1. Steps Towards Optimized Windows Azure SQL Databases | Alexandre Brisebois - November 30, 2013

    […] are the first steps that I take when I need to optimize a Windows Azure SQL Database. Then I identify the top 20 slowest queries and try to convert table scan and index scan operations into index seeks. By now, if things are […]

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s