Statistics update job

SQL Server statistics are extremely important for good query performance since they provide invaluable guidelines that allow the query optimizer to pick an optimal plan. Out-of-date or non-existent statistics objects can cause excessive I/O, poor memory utilization, and spills to tempdb, which eventually result in poor client performance.

By default, SQL Server automatically manages statistics objects by updating them on-the-fly when it detects a statistically significant portion of the data in the affected table has been modified. For many databases, this functionality works wonders, however for larger tables where the number of modifications doesn’t trigger the automatic stats updates, you may want to manage stats updates pro-actively. This post provides some T-SQL code and a SQL Server Agent job that can be used to maintain statistics.

I use a stored procedure that is marked as a system-object to enable it to be called from the context of any user database. The stored procedure code:

The procedure above stores statistics history in a table named dbo.StatsHistory. That table is defined as:

This code will create a SQL Server Agent job to run the stats update job: