I give a lot of presentations based on best practices, and in all of them I stress the importance of regularly rebuilding or reorganizing indexes. Both the REBUILD and the REORGANIZE options have their pros and cons, so I thought it would be interesting to do a poll to see which method(s) was preferred by DBAs. The results are to the left.
After seeing the results of the poll (which was not scientifically designed), I was rather impressed with how many DBAs use a combination of both the REBUILD and the REORGNIZE methods to defragment their indexes. To me, this indicates a high level of knowledge of how to best defragment indexes, as using a combination of both options allows the DBA to choose which method is best for a particular index, rather than taking the brute force method of using either REBUILD or REORANIZE to defragment all of their indexes. I was also impressed that only a very small percentage of the poll’s respondents didn’t defragment their indexes, or know what index defragmentation was.
Most recently, I have been using Ola Hallengren’s free database maintenance script to perform the defragmentation of the indexes on the SQL Server’s I manage. I really like his script because it has the ability to look at each index, and then determine, based on parameters you set, whether or not an index should be rebuilt or reorganized (or ignored if it is not fragmented). By taking this approach, you achieve a good balance between the level of index defragmentation and the resources needed to perform the defragmentation.
If you have created your own index defragmentation script, please share with us how you went about selecting which defragmentation method you use, and why.
