How can I automate statistics management?

Occasionally, it can be beneficial to turn off AUTO_CREATE_STATISTICS and AUTO_UPDATE_STATISTICS, and manage these items yourself.

I’ve created the following stored procedure, which lives in master to allow it to be called from the context of some other database. This stored procedure can be added to a SQL Server Agent Job allowing it to run periodically.

The first time the stored proc runs, it creates a dbo.StatsHistory table in MSDB which tracks updates made by the proc. If you create a “Utilities” filegroup in MSDB prior to running the stored proc the first time, the dbo.StatsHistory is automatically created on that filegroup.

Hopefully you’ll find the above code useful. Let me know if you have questions!

Ads by Google, Paying the Rent: