Woo, 2 in a row! Today I’ll be doing the second part of walking through the Maintenance Plan Wizard, Re-Organizing and Re-Building indexes.
Reorganize Index
Again, does what it says on the tin. This task kicks off an “Alter index REORGANIZE” for all the indexes in your database. It moves around the data in your index to try to put them back in the correct order. It does this by defragmenting the data at a Leaf (Smallest) level. The benefits of defragmenting in this way are that you can keep the index online so your database/index is still accessible while you are cleaning up. Still, try to schedule it for quiet times as much as possible, or more ideally, run it manually whenever you identify fragmented indexes. The general Guideline is to use Reorganize over Rebuild if your fragmentation level is < 30%. How do you find out how fragmented your indexes are? Well, this leads me off on a slight tangent.
Finding out if you have index fragmentation
This method isn’t the most optimal way to do it, but it is probably the simplest, and most legible, if you don’t want to get into using DMV’s and other low level functions. There are built in reports in SQL Management Studio which are very handy, the one you’re looking in this instance is in the screenshot below Index Physical Statistics :
