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.