Obviously seeing the actual execution plan is a big deal when tuning, and normally I enable the query plan output via SET STATISTICS XML ON and run whatever query needs some TLC, but how can I see actual record counts for historical runs of a plan or for a process I cannot easily run manually (or simulate in a test environment)?

When I pull this information from the query cache via either sys.dm_exec_query_plan or sys.dm_exec_text_query_plan only estimated row counts are shown. The same behavior exists when using the Query Store DMV, sys.query_store_plan. Since all of these DMVs are pulling actual plans that were used, I would expect to see actual row execution counts included within the graphical representations of the plan, but they're not there.

The information returned from the sys.dm_exec_query_stats DMV is only somewhat useful as it returns total counts for the statement, but detailed operator counts within the plan seem to be hidden with historical plans. With 2014, we get the sys.dm_exec_query_profiles DMV to use, which helps with plans currently being executed, but this also doesn't help when looking at historical executions.

Is this information stored elsewhere? Should I treat the Estimated Row counts as Actual Count numbers (I doubt it, but since I'm asking...)? Is there a connect item requesting this feature out there I can upvote?


1 Answer 1


You are not going to see actual row counts from the plan cache as it stores estimated plans with estimatesd row counts and compiled parameter values.

If you truly believe you need actual row counts to come up with a performance solution you have to actually execute the query or trace it using Profiler or EE specifically using the Showplan XML Event Class.

  • showplan xml statistics profile event class. Have a look at this one
    – George K
    May 10, 2017 at 15:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.