Years ago someone said “Hey – why not drop auto-created stats, since the stats you need will just get created again and you’ll end up getting rid of those you no longer need.” That *may* be a reasonable step on some systems. If the risk of bad plans on first execution of a query needed stats that have been dropped is too high, its a bad deal. If the potential concurrent cost of auto-creating dropped stats is too high, that’s a bad deal. What about analyzing query plans over some period of time to see which stats are actually used in those plans? Then auto-stats which aren’t used in that set of plans could be dropped.
That type of stats analysis could have other uses, too. Prioritizing stats manual stats updates in regular maintenance comes to mind. Or, determining what stats to create/update on an Always On Availability Group primary based on secondary activity. And troubleshooting problem queries or identifying suspicious “watchlist” stats based on highly variable queries/plans they are involved with.
So I created this blog post almost 4 years ago. And now I’ll plead with you to not use the query there… it’s awful. If you want to query trace flag 8666 style stats from plan XML, please start from the query in this post instead – its much more well behaved 🙂
Read on for the script.