Why you should not use the ConfigMgr rebuild index task

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…

Rebuild Indexes

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.

Statistics

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.

Key takeaways

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.

Advertisements
This entry was posted in ConfigMgr, SQL Server. Bookmark the permalink.

3 Responses to Why you should not use the ConfigMgr rebuild index task

  1. Pingback: Why you should not use the ConfigMgr rebuild index task | Skatterbrainz Blog

  2. Pingback: Why you should not use the ConfigMgr rebuild index task — Steve Thompson [MVP] – IT Automation Blog

  3. Pingback: Links from Exploring the wealth of ConfigMgr Community Tools at MMS – CCMEXEC.COM – Enterprise Mobility

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s