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.
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 .. ?!
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.
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.
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
