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:
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 Twitter | Follow me on FaceBook
