SQL SERVER – Denali – DMV Enhancement – sys.dm_exec_query_stats – New Columns

SQL Server version next Denali has lots of enhancements. Some of the enhancements are just game changing and overcomes needs of more coding to do the same thing.

Similar function DMV is sys.dm_exec_query_stats. There are four new columns added to this DMV. I have often used this DMV to check recently ran query, their execution plan by joining more DMVs to it. However, there was also need of knowing how many rows my queries have returned.

This DMV is enhanced with four more queries.

total_rows – Total number of rows returned by query
last_rows – Number of the rows return by the last execution of the query
min_rows – Minimum numbers of the rows returned by the query since it is compiled
max_rows – Maximum numbers of the rows returned by the query since it is compiled

Here see the quick example of the columns:

SELECT qs.execution_count,
qs.total_rows, qs.last_rows, qs.min_rows, qs.max_rows,
SUBSTRING(qt.TEXT,qs.statement_start_offset/2 +1,
(CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.TEXT)) * 2
ELSE qs.statement_end_offset END -
qs.statement_start_offset
)/2
) AS query_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
ORDER BY qs.execution_count DESC;


If you run above query it will give us different result based on your server’s workload.

You can see that the above result set – it displays how many time query has executed and how new columns (total_rows, last_rows, min_rows and max_rows) returns result based on the query.

Now here is the question back to you – if you have downloaded Denali and installed it, I would like to see you use this new column and come up with some more creative usage.

Reference: Pinal Dave (https://blog.sqlauthority.com)