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

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

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

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

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

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)

Quest

Execution Plan, SQL DMV, SQL Scripts, SQL Server
Previous Post
How to Get Status of Running Backup and Restore in SQL Server? – Interview Question of the Week #113
Next Post
What is the Difference between SUSPECT and RECOVERY PENDING? – Interview Question of the Week #114

Related Posts

3 Comments. Leave new

  • Himanshu Pandey
    March 20, 2017 11:47 am

    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

    Reply
    • 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

      Reply
  • How to get Total Rows Per Execution of Single Query/Queries?

    Reply

Leave a Reply