SQL Server Typical Maintenance Plans: Automatic Statistics Update

December 1st, 2015

Some time ago, we reviewed the automation of index defrag. Now, it is time to look at statistics.

First of all, what do we need statistics for?

During execution of any query, query optimizer is trying to build an optimal execution plan (within the limits of available information). The plan constitutes the operations sequence, by means of which you can get the result described in the query.

While selecting one or another operation, the query optimizer considers statistics, that describes value distribution for columns within a table or index, as one of the most significant input data resources.

Such evaluation allows query optimizer to create more effective query plans. At the same time, if statistics contains outdated data, the less effective operations may be selected, that may result in slow execution plans. For instance, for a small selection based on outdated statistics, the more consuming Index Scan statement is selected instead of Index Seek.

As you see, statistics must be exact and fresh to be effective for query optimizer. Sometimes, SQL Server updates statistics on its own – this behavior is regulated by the AUTO_CREATE_STATISTICS and AUTO_UPDATE_STATISTICS options.

Besides, during index rebuild, their statistics is updated automatically with selection of the FULLSCAN option, that guarantees the most exact data distribution. On the contrary, statistics update does not take place during index reorganization.

If table data is modified as often as not, it is worth performing a selective statistics update manually with help of the UPDATE STATISTICS operation.

The manual update is also quite important when the NORECOMPUTE option is selected for statistics. The option means that the automatic statistics update is not required hereafter. You can view this property (as well as other properties) in the statistics properties:

Using possibilities of dynamic SQL, let’s write a script for automatic update of outdated statistics:

While executing the script, the following statements will be generated:

The statistics obsolescence criterion depends on the situation. In our example, it is 1 day.

In some cases, an excessive statistics update for large tables may drastically decrease database productivity. That’s why, the given script is modifiable. For example, statistics for large tables can be updated less frequently:

In the next post, we will consider database backup automation.

Leave a Comment