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_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?
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