How to Find How Many Rows Each Query Returned Along with Execution Plan? – Interview Question of the Week #115

Interview question: How can you see how many rows a query returned, together with its execution plan?

Answer: For completed statements whose plans are still cached, sys.dm_exec_query_stats exposes total_rows, last_rows, min_rows, and max_rows. Join its plan handle to sys.dm_exec_query_plan to open the cached plan.

During performance-tuning engagements I am often asked for this report. It is useful, but the number of rows returned is not the same as the work done. A query can read a million rows and return one. I also check logical reads and worker time before calling a query cheap or expensive.

Here is the original query and its original SSMS result. The last column is an XML plan link in SSMS. The picture is a historical capture, so its row counts describe that instance’s plan cache at that moment, not a result to expect on every server.

SELECT DB_NAME(qt.dbid) AS database_name,
       qs.execution_count,
       qt.text AS query_text,
       qs.total_rows,
       qs.last_rows,
       qs.min_rows,
       qs.max_rows,
       qp.query_plan
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) AS qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
ORDER BY qs.execution_count DESC;

To inspect other sessions or cached execution statistics, use VIEW SERVER PERFORMANCE STATE on SQL Server 2022 and later, or VIEW SERVER STATE on earlier versions.

The DMV has one row per cached statement plan, but qt.text can contain the whole batch. If a batch has several statements, use statement_start_offset and statement_end_offset to isolate the statement you intend to tune. total_rows is accumulated over that cached entry’s completed executions; last_rows is from its last completed execution. The minimum and maximum show the range observed for that entry.

The numbers are tied to the plan’s lifetime in cache. An evicted or recompiled plan loses its entry, and a query still running is not reflected yet. This is why the report is a starting point, not a permanent count of all rows ever returned. My expensive-query DMV example is useful when the question is about reads and CPU instead.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.