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

Question: How to Find How Many Rows Each Query Returned Along with Execution Plan?

Answer:During my performance tuning consultancy engagement, this is one of the most asked questions. Everyone wants to know how many row any query returned and what is its execution plan. I personally believe this information is not so useful as what we should be concern is about how many reads any queries are doing and what is the worker time. If you are interested to know that here is the query for the same: SQL SERVER – Find Most Expensive Queries Using DMV (I would bookmark this page as it can come handy quite frequently).

Now to answer the original question here is the query which returns the query execution count, number of rows it returned along with the execution plan. If you want to see the execution plan of the query, you just have to click on the link in the last column.

SELECT
DB_NAME (qt.dbid) 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

Please note, this query only returns results from the cache. On the busy system the cache might be cleaned more frequently and you may not get accurate results. Additionally, just like any other DMV, this returns results from the time when your SQL Server services were restarted last.

Please bookmark this page if you find it useful.

Reference:Pinal Dave (http://blog.SQLAuthority.com)