It seems like all I’ve been talking about on the forums the last couple of days is the correct order of operations in a maintenance plan. The biggest confusion seems to be about the effect of rebuilding an index on statistics, with some incorrect advice being given out on what to do. Rebuilding an index will update statistics with the equivalent of a full scan – doesn’t matter whether you use DBCC DBREINDEX or ALTER INDEX … REBUILD, the effect is the same. It can do this because it sees a complete picture of the index while its doing the rebuild. Reorganizing an index (using the old DBCC INDEXDEFRAG I wrote, or the new ALTER INDEX … REORGANIZE) will NOT update statistics at all, because it only sees a few pages of the index at a time. The problem I’ve been seeing is people rebuilding indexes and then updating statistics. So why is this a problem? Well, it depends :-) So what’s the solution? The simple answer is not to update statistics on indexes that have just been rebuilt. The more complicated answer is to: Hope this helps.
Paul S. Randal
In Recovery...
14 Responses to Search Engine Q&A #10: Rebuilding Indexes and Updating Statistics
Paul,
I have once encountered a system that did not function well with ‘auto update statistics on’ and ‘auto create statistics on’. But that exception confirms the rule, in my opinion. Administrators should leave both ‘auto update statistics’ and ‘auto create statistics’ on. I never update statistics manually. Instead I rely on the full scan statistics that come for free with the index rebuilds and the auto update statistics that are triggered automatically when the contents of tables change.
Is that a bad practice?
Werner
I have made a stored procedure that supports some of the logic in Paul’s blog.
EXECUTE dbo.IndexOptimize @Databases = ‘USER_DATABASES’,
@FragmentationHigh_LOB = ‘INDEX_REBUILD_OFFLINE’,
@FragmentationHigh_NonLOB = ‘INDEX_REBUILD_ONLINE’,
@FragmentationMedium_LOB = ‘INDEX_REORGANIZE_STATISTICS_UPDATE’,
@FragmentationMedium_NonLOB = ‘INDEX_REORGANIZE_STATISTICS_UPDATE’,
@FragmentationLow_LOB = ‘NOTHING’,
@FragmentationLow_NonLOB = ‘NOTHING’,
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@PageCountLevel = 1000
How does all this work?
Indexes with a fragmentation over 30% are rebuilt, online if possible.
Indexes with a fragmentation between 5% and 30% are reorganized and also have their statistics updated.
Index with a fragmentation under 5% (or a size of under 1000 pages) are ignored.
It is available on http://ola.hallengren.com.
Hi Werner,
Your strategy is perfectly good. Some people that have a skewed data distribution find they need to update stats manually but that’s not the norm.
Thanks
Hi Ola,
Very cool. The only thing I would personally add is the ability to specify a table containing a list of object/index pairs to check the fragmentation rather than working against the whole database – but that would be the icing on the cake.
Thanks for posting!
Paul,
What if you run sp_updatestats after index maintenance?
According to BOL:’In SQL Server 2005, sp_updatestats updates only those statistics that require updating based on the rowmodctr information in the sys.sysindexes compatibility view; therefore, preventing unnecessary updates of unchanged items.
‘
Does this mean that this stored procedure will prevent unnecessary updates of statistics after index maintenance?
Thanks,
Igor
Thanks, Paul. I have this feature on the drawing board.
If you look at the solution as it is today, the parameter @Databases, works a little differently than you may think.
It allows you to select groups of databases (e.g. ‘USER_DATABASES’ and ‘SYSTEM_DATABASES’) as well as
lists of databases (e.g. ‘Db1,Db2,Db3′). It’s also possible to deselect databases (e.g. ‘USER_DATABASES,-Db1,-Db2′).
Check out the documentation. Also feel free to use it.
One implementation of the feature that you are asking for would be to be able to select and deselect,
also schemas, tables and indexes (e.g. ‘USER_DATABASES,-Db1.dbo.Table1,-Db1.dbo.Table2′).
I remember that we talked about this on MSDN Forum…
Was another great explanation and advice.
Thank you again.
Paul,
Thanks for the insights you provided in this post. I especially like your comments about the downsides of blindly updating statistics after index rebuilds (both the extra run time and reduced statistics quality issues), and the preference for informed scheduling around the index rebuild and statistics update DB maintenance processes.
I have a few questions regarding these DB maintenance processes and their impact on query plans for ongoing workloads (both in-flight queries and queries starting during the DB maintenance process operational windows):
1. When do these DB maintenance process actions invalidate existing query plans in the plan cache (if at all) – at the start of the index rebuild / statistics update run, at the completion of the index rebuild / statistics update run, or not at all?
2. Are queries able to use these resources (DB indexes, DB statistics) during the DB maintenance process run, or are they required to use query plans that exclude these resources (working at a disadvantage)?
3. Are there any blocking issues for the start of either type of DB maintenance process if in-flight queries are using these resources (DB indexes, DB statistics) that are about to be rebuilt / updated?
I have heard that some forms of index rebuilds (possibly online rebuilds only?) retain the existing index for use by query plans while the new index is rebuilt, allowing existing queries to use the current index for the duration (even if the current index is in less than ideal condition, it is likely still better than not having that index for use by other queries during the index rebuild).
I was trying to find out if these rules may apply to statistics updates – for either automatically triggered or manual requested statistics updates. I recently read about the new feature in SQL 2005 called asynchronous automatic statistics update (AUTO_UPDATE_STATISTICS_ASYNC) that appears to promote this behavior (saving the old stats while the new stats are being built, and dropping the old stats and any query plans that use them when the new stats build is complete and cutover). This new feature is available at a database level (same as auto create stats and auto update stats in the past), but is disabled by default (unlike auto create stats and auto update stats, which are both enabled by default). I understand the behavior on automatically triggered statistics updates when using this feature. I am uncertain if this behavior also applies when a manual statistics update is requested when using this feature, or how the behavior during manual statistics updates might be different with this new feature enabled or disabled.
We plan to use periodic manual DB statistics updates for an application, and wanted to determine if queries started during the manual DB statistics update run will block until the DB statistics update is completed (synchronous behavior), or will continue to process without blocking during the manual DB statistics update process run (asynchronous behavior) – either using the original outdated DB statistics until the DB statistics update process / step completes successfully or possibly without the benefit of any DB statistics.
I tried researching the topic in Books Online and on the web, but this appears to be a less common detail not covered in traditional resources. If there is an easy reference I missed, I don’t mind being put in my place. Although the topic is obscure, I believe the answers to these questions can be useful information worth sharing with the community.
Thanks for any feedback you can provide on this topic, and for all of the effort you and your colleagues at SQL Skills provide. It is greatly appreciated.
Scott R.
[...] Combining index and statistics maintenance [...]
Hello
After reading this article we disabled the Update statistics task and relayed on the full index rebuild step.
we noticed that the statistics type that starts with _WA_Sys_0000000 is not updated, which could cause severe damage.
Is it true?
uziel Elon DBA
Only index column statistics are updated by an index rebuild.
Severe damage? Of course not. Maybe incorrect plans, but not severe damage.
Hi Paul,
IF “Auto Update Statistics On” for 1 DB, will this feature invalidate recently updated stats by index rebuild.
regards,
Normally, index rebuilds will reset the rowmodctr so that auto updates will not cause recently updated index column statistics to be rebuilt again.
Great :)
Thank You