skip to content »

Updating index statistics sql

updating index statistics sql-78

4) Reorganizing an index, for example using the ALTER INDEX …

I can assure you that each of the databases in the listing above had AUTO_UPDATE_STATISTICS and AUTO_CREATE_STATISTICS both set to ON, yet the following results are indicative of statistics that can become outdated.I've provided a sample of the output generated by the collection of SQL statements executed above.As you can see, the engine still will review the statistics to see if they warrant updating.REBUILD statement, will update only index statistics with the equivalent of using WITH FULLSCAN.Rebuilding indexes does not update any column statistics.One of the questions I was asked recently while speaking at the Los Angeles SQL Server Professionals Group and the Orange County SQL Server User Group (*) was regarding the order in which jobs like rebuilding indexes or updating statistics should be executed as part of the database maintenance activities.

In general you should consider the following important points, focusing in the fact that there are two kinds of statistics: index and column statistics.

That is whether such actions are required or not for a specific index, a rebuild of the index and all the locking and churning in the logs occurs.

That is why so many of us "roll our own" index maintenance solutions as it were. Ah, at any rate, by only maintaining indexes that are fragmented, statistics updates do not occur globally against the tables/indexes in your databases. I've touched upon this in an earlier tip, SQL Server's engine will update the statistic when: Based upon this criteria, there will be many cases where the underlying data changes in such a way or in such levels that the statistics that exist for an index will not be indicative of the actual data in the database.

Of course, the worst case scenario would be if you first rebuild your indexes, which also updates the index statistics by scanning the entire table, and later you run UPDATE STATISTICS using the default values, which again updates the index statistics but this time only with a default and smaller sample.

Not only you are updating your index statistics twice but you are overwriting the better of the two choices.

Using UPDATE STATISTICS WITH FULLSCAN will scan the entire table.