Rebuilding the database indexes is a good thing, yes? Absolutely.
Why not use the ConfigMgr rebuild index maintenance task?
When the maintenance task works, and I have proof that some of the time it fails to run on ConfigMgr:
- It REINDEXES all indexes with more than 10% fragmentation
- It does not update statistics
Let’s break these two items down…
By updating all indexes, the SQL Engine has to plow through EVERY index and REINDEX the index, REGARDLESS if it really needs the update! This makes the process very inefficient. You would not notice this in a lab, or a small environment. However, you WILL notice it on a very large site. To review the gory details, history time here.
It is far more efficient to Reorganize indexes that are slightly fragmented and Rebuild indexes that are very fragmented.
Database statistics are interesting to me… if you have ever studied statistics, you’ll know that the sampling method of any data will lead to really good results or perhaps, poor results. SQL statistics are used to map the distribution of values in a given column. The SQL Server query optimizer uses statistics to determine which indexes (if any) will be used to generate the query plan, then subsequently execute the query based on that plan.
If the statistics are up-to-date, then, most of the time the proper indexes will be selected for the query plan, leading to an efficient query plan execution. However, if the statistics are NOT up-to-date, expect that the WRONG query plan will be selected and a poorly executed query will result.
The analogy I like to use here, lets say you travel to an unfamiliar city. You pick up your rental car, and you are handed a street map for that city (this is pre GPS). If that street map is 10 years old (or older), you may have trouble finding your destination due to the changes in the road systems! You need an up-to-date map to determine an efficient route.
Updating indexes without updating statistics is a disaster waiting to happen.
Do use Ola Hallengrens solution to INTELLIGENTLY update the indexes and modified statistics. Your database will be happier, your users will be happier.