Posted May 27, 2014

Written by Steve Thompson

Original Post – http://myitforum.com/myitforumwp/2014/05/27/configmgr-2012-index-optimization/

ConfigMgr 2012 Index Optimization

A recent blog post on index defragmentation and comments deserve some space here.

The blog:

https://blogs.technet.com/b/smartinez/archive/2014/03/28/talking-database-in-configmgr.aspx

The blog does a really good job exposing how the Index optimizer decides to rebuild indexes. That is a good thing!

Let’s walk through some of the key points:

This query is what ConfigMgr executes when running the Rebuild Index site maintenance task

SELECT DISTINCT sch.name + ‘.’ + Object_name(stat.object_id),
ind.name,
CONVERT(INT, stat.avg_fragmentation_in_percent)
FROM sys.Dm_db_index_physical_stats(Db_id(), NULL, NULL, NULL, ‘LIMITED’) stat
JOIN sys.indexes ind
ON stat.object_id = ind.object_id
AND stat.index_id = ind.index_id
JOIN sys.objects obj
ON obj.object_id = stat.object_id
JOIN sys.schemas sch
ON obj.schema_id = sch.schema_id
WHERE ind.name IS NOT NULL
AND stat.avg_fragmentation_in_percent > 10.0
AND ind.type > 0
ORDER BY CONVERT(INT, stat.avg_fragmentation_in_percent) DESC

[ST] Identify any table index with more than 10% fragmentation.

EXEC Sp_msforeachtable
@command1=“print ‘?’ DBCC DBREINDEX (‘?’, ‘ ‘, 80)”

[ST] The DBCC DBREINDEX rebuilds an index for a table or all indexes defined for a table.

Using the sp_MSforeachtable will run this statement for each table, allowing an 80% fill factor for each index. (20% index free space will reserved for additional index growth). Assumption is made here that the re-indexing will occur for each table located within the previous query.

The remainder of the article shows as an alternative to the (broken) maintenance plan how to create an SQL Server Maintenance Plan Wizard to rebuild indexes and update statistics.

Then summarizes with how to validate the degree of index fragmentation, both before and after.

Now to the comment…

…that basically involves creating a maintenance database and then running Ola Hallengren’s SQL script against it to create the necessary objects and then run the index optimizer script against the ConfigMgr 2012 database on a weekly basis. There seems to be some, dare I say disagreement, amongst some SCCM MVPs regarding the best way to go about doing this. Some of the MVPs believe that the Rebuild Indexes Site Maintenance Task in ConfigMgr 2012 still has some issues. Therefore, some of them are stating that the best method is to keep the Rebuild Indexes disabled and then use the Steve Thompson method. I just want a consensus and use a method that is going to work, but one that is not going to create additional overhead for me.

Summary

Consensus is always a good thing, however, it is always not that simple or easy. Also, I do not believe there is really any disagreement within the MVP community about this information. In light of that, I’d like to share what I know.

I first discovered Ola’s excellent index and statistics optimization task after looking for a solution to the Configuration Manager Index Maintenance task not always running. We’ve seen this failure on SCCM 2007 and ConfigMgr 2012. This has been reported by other MVPs, and observed first hand by this MVP on multiple occasions, on multiple sites. This led to a presentation at MMS 2013:

http://channel9.msdn.com/Events/MMS/2013/UD-B407

The script usage was documented here:

http://stevethompsonmvp.wordpress.com/2013/05/07/optimizing-configmgr-databases/

Ironically, at the MMS 2013 conference, we learned that Microsoft IT that is responsible for running their ConfigMgr 2012 site runs Ola’s Index Maintenance script as well.

What makes Ola’s script unique, it does not take a blunt force approach to rebuilding every index for every table, rather only rebuild indexes that need to be reorganized or rebuilt if necessary. And, this is based on thresholds you can configure.

Other advantages:

Further, it will only update the Statistics that need to be updated.

If indexes are only lightly fragmented a reorganization might be the most efficient method. However, if heavily fragmented (>30%), then it might be faster to rebuild (essentially drop and create an index).

It is possible to set the number of pages the index occupies before running any tuning… if the index falls on less than 1,500 pages, SQL will likely read the entire index into memory. No real need to optimize that index scenario.

Furthermore, the more frequently this task is run, the less time it takes to complete, because there is less work to do!

Conclusions

Will the other method optimize your indexes and statistics? Yes it will.

Is it better than a non-functional site maintenance task? Yes it is.

Is it the most efficient way to optimize a ConfigMgr database? Not in my opinion. Further, this approach does not scale well to very large sites and/or databases.

My recommendation, do your own benchmark testing and then decide.