SQL Server 2012: DENALI-new columns added to sys.dm_exec_query_stats

Dear All,

I have started exploring new DMVs and some enhancements to existing DMVs in SQL Server 2012 (DENALI).

Today, I will talk about sys.dm_exec_query_stats – this DMV is heavily used to troubleshoot long running queries. Four new columns have been added to this DMV which are as follows:

Description of columns extracted from MSDN)

total_rows bigint Total number of rows returned by the query. Cannot be null.
last_rows bigint Number of rows returned by the last execution of the query. Cannot be null.
min_rows bigint Minimum number of rows returned by the query over the number of times that the plan has been executed since it was last compiled. Cannot be null.
max_rows bigint Maximum number of rows returned by the query over the number of times that the plan has been executed since it was last compiled. Cannot be null.

For example; run the following query multiple times, say 10:

select * from Northwind2.dbo.BigOrders

And then run the following query:

select DEST.text, DEQS.last_rows,DEQS.total_rows from sys.dm_exec_query_stats DEQS
CROSS APPLY
sys.dm_exec_sql_text(plan_handle) DEST
ORDER by last_rows DESC

You will observe the following output:

   

1_SQL_Server2012_DENALI_new_columns_added_to_sys.dm_exec_query_stats

Where last_rows shows the number of rows returned by last execution and total_rows shows a cumulative number of rows returned by the same query (when executed multiple times with the same plan)

These new columns can give more insight while troubleshooting problematic queries.

 

Regards

Rahul Sharma

Like us on FaceBook Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on TwitterFollow me on FaceBook

   

Leave a Reply

Your email address will not be published.