March Madness – SQL Azure – sys.dm_exec_query_stats

Yesterday we saw how to view the query plan for the statements hitting your SQL Azure instance. Looking at the statement and the plan itself is good, but it does not give you a complete picture for performance tuning. For that you will need some historical context. One way to get such context is to look at the sys.dm_exec_query_stats system view. This view will show you the aggregate totals for all query plans currently in cache. Yes, that’s right…once a plan is pushed out of your plan cache then so will the row disappear from the sys.dm_exec_query_stats view.

Go ahead and run this query against your SQL Azure instance:

SELECT *
FROM sys.dm_exec_query_stats

The columns in the result set should match what you have in SQL 2012. Now, that statement by itself doesn’t do much for you, so we need to join this to some of the view we have discussed already this week. Here’s an example (courtesy of Buck Woody):

SELECT TOP 5 query_stats.query_hash AS [Query Hash],
 SUM(query_stats.total_worker_time) / SUM(query_stats.execution_count) AS [Avg CPU Time],
 MIN(query_stats.statement_text) AS [Statement Text]
FROM
 (SELECT QS.*,
 SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1,
 ((CASE statement_end_offset
 WHEN -1 THEN DATALENGTH(st.text)
 ELSE QS.statement_end_offset END
 - QS.statement_start_offset)/2) + 1) AS statement_text
 FROM sys.dm_exec_query_stats AS QS
 CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST) as query_stats
GROUP BY query_stats.query_hash
ORDER BY 2 DESC;

That’s good, but I’m usually one that likes to see more details. Let’s take a look at this example from Glenn Berry, where he shows us how to find the query with the top average for CPU time:

SELECT TOP (25) MIN(query_stats.statement_text) AS [Statement Text],
SUM(query_stats.total_worker_time) / SUM(query_stats.execution_count) AS [Avg CPU Time],
query_stats.query_hash AS [Query Hash]
FROM (SELECT QS.*, SUBSTRING(ST.[text], (QS.statement_start_offset/2) + 1,
    ((CASE statement_end_offset
        WHEN -1 THEN DATALENGTH(st.[text])
        ELSE QS.statement_end_offset END
            - QS.statement_start_offset)/2) + 1) AS statement_text
     FROM sys.dm_exec_query_stats AS QS
     CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) AS ST) AS query_stats
GROUP BY query_stats.query_hash
ORDER BY [Avg CPU Time] DESC;

I like that one, but it is only focused on CPU. This query will show me the top ten statements and allows for me to quickly change my focus by altering the ORDER BY clause (courtesy of David Pless):

SELECT TOP 10
    (total_logical_reads/execution_count) AS avg_logical_reads,
    (total_logical_writes/execution_count) AS avg_logical_writes,
    (total_physical_reads/execution_count) AS avg_phys_reads,
    (total_worker_time/execution_count) AS avg_cpu_over_head,
total_logical_reads, total_logical_writes, total_physical_reads,
total_worker_time, execution_count, total_elapsed_time AS Duration,
plan_generation_num AS num_recompiles,
statement_start_offset AS stmt_start_offset,
    (SELECT SUBSTRING(text, statement_start_offset/2 + 1,
        (CASE WHEN statement_end_offset = -1
            THEN LEN(CONVERT(nvarchar(MAX),text)) * 2
                ELSE statement_end_offset
            END - statement_start_offset)/2)
     FROM sys.dm_exec_sql_text(sql_handle)) AS query_text,
      (SELECT query_plan FROM sys.dm_exec_query_plan(plan_handle)) AS query_plan
FROM sys.dm_exec_query_stats a
--JUST CHANGE THE ORDER BY TO GET THE OTHER RESOURCES
ORDER BY (total_logical_reads + total_logical_writes)/execution_count DESC

That’s gold as far as I am concerned. I can quickly poll my SQL Azure instance and get a wealth of information and store it for later analysis.

Tomorrow we will look at one way you can help query performance in SQL Azure.