Recently, at one of our customer sites, we noticed this interesting anomaly.

In this database, there are several tables and each table has several statistics. Statistics could be created in several ways.

• Index based statistics
• Column Statistics
  • User created
  • Auto created Statistics (that are created by Sql Server)

Problem

When they perform nightly maintenance, some how the ‘auto created’ statistics were not getting updated. The ‘is_auto_update_stats_on’ & ‘is_auto_update_stats_async_on’ options are set to 1. But still some statistics are not getting updated.

Usually, there are many ways to update statstics:

• Use maintenance plan to perform ‘Update Statics’ (most popular)
• Use “UPDATE STATISTICS” command to update selective tables or selective statistics
• sp_updatestats : Updates all statistics in the entire database
• etc

In a database environment, given the size and traffic and possible maintenance window duration, the option we choose varies. If you have sufficient maintenance window, we could use a SQL Job to update statistics on all tables in a database using a maintenance plan approach or ‘sp_updatestats’. But in some cases, we may not have enough time to run updates on all statistics. So, we end up selecting a list of statistics and running “UPDATE STATICS <stats_name>” on each of them.

Solution

Guess what happend, in this case. Yes, the other (auto created) statistics will not be updated. And this is what happened at our customer site. They could not afford to perform complete update stats each night and ended up choosing a few stats each night. This inadvertently resulted in ‘auto created’ statistics from not getting updated.

Index stats will be updated when you rebuild indexes.

The lesson is not to depend on the database settings alone (‘is_auto_update_stats_on’ & ‘is_auto_update_stats_async_on’). Have your own maintenance plans to update statistics all on all tables.

Options:

1. Use UPDATE STATISTICS
2. Use sp_updatestats
3. Use maintenance plans

Option 1

Rather than choosing each statistic name in “UPDATE STATISTICS” command, just give the table name. It updates all statistics on that particular table. Below is the image from our lab server as I was running UPDATE STATISTICS <table name>.

In the below image, is a snapshot from the table while the statistics were being updated. Hence you see some are updated and some are still in the process of getting updated. Interesting point is that the ‘auto created’ statistics also get updated using this command with table name.

Result

Update Statistics

Option 2

Result

UpdateStatics with Procedure

Option 3

Use maintenance plan to perform “Update Stats” on the entire database. This in turn calls UPDATE STATISTICS on each table resulting in updating both user & auto created stats.

Hope this helps,
_Sqltimes