SQL Server Denali, the next version of SQL Server, comes with a plethora of enhancements that are set to revolutionize the way we work with databases. One such enhancement is the addition of new columns to the sys.dm_exec_query_stats DMV, which provides valuable insights into the execution statistics of queries.

Previously, while using the sys.dm_exec_query_stats DMV, we could check the recently executed queries and their execution plans by joining it with other DMVs. However, one crucial piece of information was missing – the number of rows returned by the queries. With the new enhancements, this DMV now includes four additional columns:

• total_rows: Total number of rows returned by the query
• last_rows: Number of rows returned by the last execution of the query
• min_rows: Minimum number of rows returned by the query since it was compiled
• max_rows: Maximum number of rows returned by the query since it was compiled

Let’s take a look at a quick example that demonstrates the usage of these new 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;

When you run the above query, the result set will vary based on your server’s workload. However, it will display the number of times the query has been executed, along with the values of the new columns (total_rows, last_rows, min_rows, and max_rows) that provide insights into the number of rows returned by the query.

Now, here’s a challenge for you – if you have downloaded and installed SQL Server Denali, I encourage you to explore the usage of these new columns and come up with some creative ways to leverage this valuable information in your own projects.

Stay tuned for more exciting features and enhancements in SQL Server Denali!