question

WilliamD avatar image
WilliamD asked

Should I defragment system indexes?

Hi everyone, I am currently using [Ola Hallengren's Index Maintenance Script]( http://ola.hallengren.com) to perform index defrag and reorganisation. I have made a few "upgrades" to the script and am discussing with him about the possibility of maintaining the indexes on tables shipped by microsoft (soft-system tables?). I see this as quite harmless, in that a reorg or rebuild cannot break anything. I have activated this for one of my systems that has replication running. The distribution database sees lots of activity and thus the indexes get hammered over time. I cannot admit to baselining this DB, but know that a table that has a few million rows and high fragmentation is not a good thing! What I would like to know is, would you recommend index maintenance on system tables/indexes or not? Ola is not too sure whether he can add this as an option to his script, as the MS standard with Maintenance Plans is to leave these out (user tables with the attribute ms_shipped = 0). I can understand his concerns, but what can go wrong? Any thoughts on this are greatly appreciated.
administrationindexesmaintenancesystem-tablesola-hallengren
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack avatar image
Fatherjack answered
I have defragmented the indexes that you mention (ie those pertaining to replication) with no ill effects. I would to have to confess to not benchmarking before or after but as you say 1M rows and 85% frag cant be good. I ran the script and there was no smoke. I use a variation of the defrag script from BoL and havent seen how Ola's script works, to my knowledge the BoL script doesnt have any filter other than the size and the fragmentation level. Some indexes are too small to actually defragment and some frag %ages are just too little to worry about. I also added a 'wrapper' script that changes filter values based on day and time - eg SAT+SUN will work on anything over 20% whereas MON-FRI only tackle 40%+ not sure if this is the categorical recommendation that you were looking for .. ?!
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks for the info. I am not expecting an answer that will satisfy everything. It is more a call for scenarios that could cause a problem. Ola just mentioned in an email that backups could fail if the msdb backup tables are being reorged/rebuilt offline.
0 Likes 0 ·
+1 for "...and there was no smoke ..."!
0 Likes 0 ·
Blackhawk-17 avatar image
Blackhawk-17 answered
I have modified Ola's script (the best out there) and included a VBScript to clean up old logs as well :) and have no issues defragging system dBs. In most cases they don't need it to be honest. For us the bottom line is they are databases and require maintenance just like any other.
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks for your viewpoint. Also good to know another one out there *dares* to do the same :o) About the VBScript. Have you seen SQL Server Finebuild ( http://sqlserverfinebuild.codeplex.com/) - does a very good job of setting up similar things as that. It is extremely detailed, and customisable.
0 Likes 0 ·
Ola Hallengren avatar image
Ola Hallengren answered
In the most recent version of my scripts, there is a job to cleanup old output files. It was introduced on 16 May 2010. [ http://ola.hallengren.com/versions.html][1] IndexOptimize is selecting indexes with ms_shipped = 0. The background for this is that the Maintenance Plans works this way. So I thought that Microsoft must have some reason for not rebuilding or reorganizing indexes on ms shipped objects. One reason could be that they would like to avoid locking on ms shipped objects. The backup command is for example writing to the msdb backup tables and if they are locked this is not working and the backup command reports failure. Maybe there could be other similar things. I will try to get some information from Microsoft. [1]: http://ola.hallengren.com/versions.html
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.