queries with significant difference of estimated and actual number of rows - skew data - in the query plan

Question

I am using dbcc show_statistics looking for skew data in my histogram and improving the quality of my statistics.

OPTIMIZE FOR UNKNOWN doesn't use a value - instead, it uses the density vector.

If you run DBCC SHOWSTATISTICS, it's the value listed in the "All density" column of the second result set.

On the pictures below, due to skew data, there is a difference between the estimate and the actual number of rows.

How do I effectively handle very skewed data?

this talks about @variables and recompile, which can help and a part of a solution.

Filtered statistics seems to be more likely the solution in this case.

Question:

How do I find in the cached execution plan, the queries that have difference between estimate and actual number of rows?

Answer 1

There is no perfect answer. First, and most important, work on figuring out how much, how often, and in what manner, you may need to refresh your statistics manually. The automatic maintenance is good enough for 80-90% of your stats, but it's grossly inadequate for the remainder, so step one, get manual updates involved. How frequent is really dependent on your data and the rate of change. Also, sampled versus full scan, another thing you'll have to determine based on your data, the distribution, and the rate of change.

That done, you may still see skew and see it negatively affecting your query performance. There are a bunch of possible tools for dealing with this, none of them "flawless, one size fits most" type of solutions. Instead, everything is "it depends".

One way is RECOMPILE (at the statement level, please) hints. However, that adds overhead to the processor since it will be recompiling the plan. It also negatively affects plan reuse, since you won't see any.

Another is to use the optimizer hint, OPTIMIZE FOR, UNKNOWN if an average of the data works better, or a value if a more specific plan will work. Again, more testing.

Another tool is to use Query Store and Plan Forcing if you're running SQL Server 2016 or greater. Similar to using OPTIMIZE FOR a value, but no code changes required, so it's a win.

SQL Server 2022, coming out soon, also has automated parameter sensitive plan tuning that automates Query Store plan forcing (it's cool, very cool, but won't solve every issue).

Finally, you can also get into using filtered statistics or filtered indexes to create specific statistics for specific queries. It's a lot of work, but can solve the problem. Again, testing.

Any one of these can work, but none of them will work all the time. You will have to be prepared to try a different solution, depending on the situation.

As to querying for estimates vs. actual, the issue you hit is that the plans in cache, and query store, don't hold the runtime metrics, so there's no way to use those to compare. However, if you're running SQL Server 2019, CTP 2.4 or greater, you can use sys.dm_exec_query_plan_stats to see the last plan with runtime metrics. This isn't perfect since one execution may match the estimates while the next does not, but this is the easiest way to solve this issue. Otherwise, and I REALLY recommend against this, you'd have to capture the plans using Extended Events in order to be able to run comparisons.