Index reorg/rebuild script

Index fragmentation may be causing more I/O than necessary for efficient query processing. Fragmentation occurs as a result of inserting items into the middle of the index instead of appending them to the end. Inserting items into an index causes the page where the items are being inserted to “split” into two pages. The resulting fragmentation can make it harder for SQL Server to find the rows required for a given query. An index reorg can fix various performance issues. Before blindly running an index reorg, there are several things to consider.

Erik Darling discussed when to consider rebuilding or reorganizing an index – take his advice – he knows what he’s talking about! For the record, doing a reorg or rebuild of data stored on a SAN, a RAID array, or an SSD disk is unlikely to have an appreciable effect on performance, but hey, you’ve been warned – rebuild away!

We’re not talking about this kind of rebuild, ok?! – wireheadinc

If you have a lot of page splits, as is the case with using a non-sequential ID value for the index key, and you’re storing your data on a standard hard drive, you may see performance decrease inversely with index fragmentation. The script below will automatically defragment indexes that meet certain requirements. Requirements include degree of fragmentation, and size of the index. You can specify when to switch between reorganizing an index versus completely rebuilding it.

If you do decide you need to rebuild indexes on an ongoing basis, and have determined that automating statistics updates isn’t cutting it, you might consider using this script. The script only updates row-store clustered, non-clustered, and XML indexes.

This script should work on all versions of SQL Server since SQL Server 2012. Let me know if you found this script useful, or if you have a comment or question about it.

Also, check out the rest of our SQL Server Tools!