How to include "Actual Row" counts in Execution plans pulled from the Query Cache or Query Store?

by John Eisbrener   Last Updated May 10, 2017 15:06 PM

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?

Answers 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.

George K
George K
May 10, 2017 14:41 PM

Related Questions

Plan cache mystery

Updated July 23, 2018 20:06 PM

SQL Server Plan Guide

Updated March 03, 2017 05:06 AM