Sql-server – How to include “Actual Row” counts in Execution plans pulled from the Query Cache or Query Store

dmvexecution-plansql server

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?

Best Answer

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.

Related Solutions

Sql-server – Inaccurate ‘Actual’ row counts in parallel plan

Rows are passed across exchanges internally from producer to consumer thread in packets (hence CXPACKET - class exchange packet), rather than a row-at-a-time. There is a certain amount of buffering inside the exchange. Also, the call to shut down the pipeline from the consumer side of the Gather Streams has to be passed in a control packet back to the producer threads. Scheduling and other internal considerations mean that parallel plans always have a certain 'stopping distance'.

As a consequence, you will often see this sort of row count difference where less than the whole potential rowset of a sub-tree is actually required. In this case, the TOP brings execution to an 'early end'.

More information:

Sql-server – How bad is query plan pollution (too many equivalent plans in cache)

"How bad is it?" depends on the degree to which you are suffering now or could suffer with increased workload in the future.

One major point of suffering with plan cache pollution could be too many single use plans bloating your plan cache leading to inefficient cache usage.

Another point of suffering could be high compilations/second - so in an environment with a heavy workload and a lot of activity, there is a cost associated with compiling over and over.

You can see the impact of compilations/sec in perfmon (SQL Server Statistics:Compilations/sec). This can look like CPU pressure. To your performance/applications, this can look like increased query duration waiting for needless compiles each time it runs.

You can see the impact to the plan cache from the memory bloat by this query borrowed from Glenn Berry's Diagnostic scripts. How big is your SQLCP plan cache?

SELECT TOP(10) [type] AS [Memory Clerk Type], 
       SUM(pages_kb)/1024 AS [Memory Usage (MB)] 
FROM sys.dm_os_memory_clerks WITH (NOLOCK)
GROUP BY [type]  
ORDER BY SUM(pages_kb) DESC OPTION (RECOMPILE);

Also the query that was used in the question to identify the number of plans helps as well.

Is This Ever a Good Thing?

There are some cases where this could be good, but the situation is rare. Basically if you were suffering from parameter sniffing gone bad (nutshell: if the data can vary widely from execution to execution based on parameters, one compilation for one set of parameters ideal may yield an excellent query plan for that one query but poor for others.). My guess is that you likely wouldn't be dealing with that as bad as the implications from poor plan reuse.

What Can You Do About It?

Optimize For Ad Hoc Workloads can certainly help with the memory implications since only a stub of the plan is stored in cache at first execution, and the full plan isn't stored until it is executed a second time with the same plan.
Forced Parameterization could help here also. It can sometimes force parameterization to happen and help solve both the issue of cache bloat and the cost of having to recompile.
Fix The Queries Ideally, you shouldn't have to resort to these options, but instead can be more strict in your database development, encourage plan reuse, consider stored procedures for all of their benefits, and attempt to head off the problem that way. The ways to help fix this through forced parameterization or optimize for ad hoc are good to help, but the best solution is always aimed at the root cause.

There is an excellent resource here that talks about some of the dangers of plan cache pollution and some things you can do. I'd recommend a read here. It is written for SQL Server 2012, but the concepts and solutions apply.

Best Answer

Related Solutions

Sql-server – Inaccurate ‘Actual’ row counts in parallel plan

Sql-server – How bad is query plan pollution (too many equivalent plans in cache)

Related Question