User-Created Statistics In Place
sp_Blitz® Result: User-Created Statistics In Place
Statistics are metadata about our tables and columns that help SQL Server build a better execution plan. SQL Server automatically creates and updates statistics for us, and it’s fairly rare that DBAs have to go in and add statistics manually.
Extra statistics aren’t really a problem – most of the time.
- Updating them can take a long time if you set it up wrong. Kendra explains why in her post UPDATE STATISTICS: The Secret IO Explosion.
- User-created statistics can make some ALTER TABLE commands fail. If someone needs to modify or remove a column and a user created statistic is on it, whoops, you may have just caused that code release to fail!
This part of our SQL Server sp_Blitz® script checks sys.stats looking for user_created = 1.
To FIX THE PROBLEM
Look in the affected database for the statistics involved:
SELECT * FROM sys.stats WHERE user_created = 1
Start asking questions about why those stats are in place. You don’t have to drop them immediately, but if you’ve got rampant user-created stats on your largest tables, it can make your maintenance job runtimes much longer. If you’re under pressure to make UPDATE STATS happen faster, you could consider removing these statistics.