By David Pless – dpless@microsoft.com

Changes to sys.dm_exec_query_stats

In a previous article we discussed the ‘Mother of all DMV Queries’, aptly named because of the ability to
get so much information by simply changing the order by columns and mapping to two Dynamic Management Functions
sys.dm_exec_sql_text and sys.dm_exec_query_plan.

In this article we are going to look at a variation of this query again to look at the improvements introduced
in ‘SQL Server 2008 R2 SP1′

Summary
The sys.dm_exec_query_stats is critical because it allows us to see the following information for each statement
within a query plan. Below is a summary of the columns available in the sys.dm_exec_query_stats output.

sys.dm_exec_query_stats columns SQL Server 2008 R2 Pre-SP1

• sql_handle- Identifies a SQL statement used to get the statement from sys.dm_exec_sql_text
• plan_handle- Identifies a SQL plan used to get the statement from sys.dm_exec_query_plan     
• statement_start_offset – Used to identify a statement within a batch
• statement_end_offset – Used to identify a statement within a batch         
• creation_time     
• last_execution_time        

 SQL Server Resources:

• plan_generation_num – The number of times a statement was recompiled
• execution_count – The number of times a statement was executed

 There are total, min, max, and last columns for the following resources:

• worker_time (CPU)
• physical reads
• logical writes
• logical reads
• clr time
• elapsed_time (Duration)

 The following can be used to uniquely identify SQL Server text and plans which was introduced in SQL Server 2008.

• query_hash
• query_plan_hash              

For more information on sys.dm_exec_query_stats see the following:
http://msdn.microsoft.com/en-us/library/ms189741.aspx

 New in Service Pack 1 is the following columns:

• total_rows
• last_rows
• min_rows
• max_rows

This information can be very useful when trying to track volume between executions including the average number
of records. A common need for this is in a reporting / data processing environment where the volume of records
can easily slow down reporting. When you are trying to discern what is slowing down a report, it is very useful to be
able to track the number of records over time.

Below is an example of how these new columns added to SQL Server 2008 SP1 (CTP) can be leveraged:

SELECT
TOP 10 qs.*, (total_rows/execution_count) AS AVG_ROWS, qp.query_plan, st.text
FROM
sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) as qp
–SPECIFY A TIMEFRAME TO GATHER CACHED DATA
–WHERE DATEDIFF(hour, last_execution_time, getdate()) < 1 — change hour time frame
–CHANGE THE ORDER BY TO GET THE TOP 10 for DIFFERENT RESOURCES
ORDER BY (total_rows/execution_count) DESC;

By David Pless – dpless@microsoft.com

I call the included query below the ‘Mother of all DMV Queries’ because it is very powerful as it can grab the most expensive statements and their execution plans for several different resources such as CPU, duration, execution count, and logical reads, and more.

The included query is a building block query that can be found in many different articles, but is a critical tool for addressing the top consumers of any SQL Server environment post SQL Server 2005. Our included query uses the sys.dm_exec_query_stats DMV (Dynamic Management View) to get the bulk of the information. Other functions are used to grab the text and the execution plan.

From Books Online the sys.dm_exec_query_stats DMV, ‘returns aggregate performance statistics for cached query plans. The view contains one row per query statement within the cached plan, and the lifetime of the rows are tied to the plan itself. When a plan is removed from the cache, the corresponding rows are eliminated from this view.’ This means that once the SQL Server is recycled or the plans are removed from cache, then the references and hence the information is also not accessible.

Note: Though there is not an unlimited amount of time available to access this information, there are several methods available that can be used to harvest this data such as the DMVStats tool (http://sqldmvstats.codeplex.com/) from Codeplex.com or by using the Management Data Warehouse (MDW) that was introduced in SQL Server 2008. We will cover both of these technologies in a future set of articles.

Reference: sys.dm_exec_query_stats (BOL)
http://msdn.microsoft.com/en-us/library/ms189741.aspx

By using the sql_handle you can access the actual text of the SQL Server statement and by using the plan_handle you can access the Showplan in XML format for the batch specified by the plan handle. The plan specified by the plan handle can either be cached or currently executing. In order to get this information you have to utilize two Dynamic Management Functions and utilize the ‘CROSS APPLY’ method to get the text and the plan for each sys.dm_exec_sql_text reference.

References: sys.dm_exec_sql_text (BOL)
http://msdn.microsoft.com/en-us/library/ms181929.aspx

sys.dm_exec_text_query_plan (BOL)
http://msdn.microsoft.com/en-us/library/bb326654.aspx

Though we are only showing one example of obtaining the sql_handle, you can also obtain the sql_handle from the following dynamic management objects:

• sys.dm_exec_query_stats
• sys.dm_exec_requests
• sys.dm_exec_cursors
• sys.dm_exec_xml_handles
• sys.dm_exec_query_memory_grants
• sys.dm_exec_connections

We will cover using some of these Dynamic Management Views in a future article.

Note: For batches, the SQL handles are hash values based on the SQL text. For database objects such as stored procedures, triggers or functions, the SQL handles are derived from the database ID, object ID, and object number. plan_handle is a hash value derived from the compiled plan of the entire batch.

There are some situations where the query plan cannot be obtained by leveraging the sys.dm_exec_query_plan Dynamic Management Function, for example consider the points below.

Some SQL statements are not cached, such as bulk operation statements or statements containing string literals larger than 8 KB in size. XML Showplans for such statements cannot be retrieved by using sys.dm_exec_query_plan unless the batch is currently executing because they do not exist in the cache.

If a Transact-SQL batch or stored procedure contains a call to a user-defined function or a call to dynamic SQL, for example using EXEC (string), the compiled XML Showplan for the user-defined function is not included in the table returned by sys.dm_exec_query_plan for the batch or stored procedure. Instead, you must make a separate call to sys.dm_exec_query_plan for the plan handle that corresponds to the user-defined function.

When an ad hoc query uses simple or forced parameterization, the query_plan column will contain only the statement text and not the actual query plan. To return the query plan, call sys.dm_exec_query_plan for the plan handle of the prepared parameterized query. You can determine whether the query was parameterized by referencing the sql column of the sys.syscacheobjects view or the text column of the sys.dm_exec_sql_text dynamic management view.

Additionally, the number of due to a limitation in the number of nested levels allowed in the xml data type, sys.dm_exec_query_plan cannot return query plans that meet or exceed 128 levels of nested elements. In earlier versions of SQL Server, this condition prevented the query plan from returning and generates error 6335.

In SQL Server 2005 Service Pack 2 and later versions, the query_plan column returns NULL. You can use the sys.dm_exec_text_query_plan dynamic management function to return the output of the query plan in text format.

The sys.dm_exec_text_query_plan dynamic management function was introduced in SQL Server 2005 Service Pack 2. This DMF was is also leveraged in the Performance Dashboard reports that can be found here: http://www.microsoft.com/downloads/en/details.aspx?FamilyID=1d3a4a0d-7e0c-4730-8204-e419218c1efc&DisplayLang=en

We will discuss the SQL Server 2005 Performance Dashboard in a future article.

For more information on leveraging the sys.dm_exec_sql_text, see the following article:
Reference: sys.dm_exec_sql_text (BOL)
http://msdn.microsoft.com/en-us/library/ms181929.aspx

Example:

From this single DMV below we can get the statement and plan for the top consumers driven simply by the ORDER BY clause. Averages can be obtained in the SELECT and ORDER BY by dividing by the execution count.

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