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)
3 Comments. Leave new
Hi Pinal sir,
Its my pleasure I am writing you. Thanks for your useful stuffs, it really helps the folks a lot.
Sir, recently one of my friends was asked a question during the interview, we searched it everywhere but did not find any satisfactory answer. Then I decided to bring it up with you and I am sure we would be getting our answer here. The question being-:
‘ I am having a very long query( nearly about 2 pages) and it contains many sub queries. Now I discover that my query is taking a lot of time. I want to know which part of my query is taking time or running slow in fetching the data’
I have found that most of the questions in an interview are similar to performance only, like how to boost performance of your queries, how to do performance tuning and all.
it would be great of you could provide me some links for the same.
Regards
Himanshu
Run sp_whoisactive
It will give you the exact portion of the code thats running at the time including what its waiting on and more
Crack open the execution plan which will reveal more information about operators used and go from there
If youre running on SQL 2016, consider enabling query store
How to get Total Rows Per Execution of Single Query/Queries?