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:

And then run the following query:

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