Indexes, indexes, indexes my kingdom for an index! Well, something like that. Indexes are the single most important structure in our relation database world. So, it is understandable that I do everything I can to gather information, monitor their health and maintain them to the best of my ability. One of the keys to index health is how badly fragmented the index is. Many people simply reindex everything on a regular schedule. While this may be fine for smaller indexes say under 50 megabytes but not so great for indexes that may be very large 400 megabytes or more. Knowing how your database is used is also needed if you are going to truly plan for the future and your index health. Is your table built with an identity for the primary key and only receives inserts? Or is it indexed on a GUID and has heavy deletes and updates? In the first case, depending on fill factor you may not need to do full reindex with a high level of frequency. In the second case, you may not be able to keep fragmentation levels under control without sacrificing the availability of your database. The problem is the larger the index the more difficult it becomes to manage from a reindexing strategy. When indexes get in the gigabyte range things like partitioning become as much a necessity for performance as well as maintenance tasks. For years I’ve written and maintained my own index maintenance scripts. Well, that all came to an end when Michelle Ufford put out her index defrag script.

Series to Date SQLDIY: Manage and Monitor SQL Server Yourself

Link to the script Index Fragmentation Levels

I’m hosting all my scripts from this series on GitHub as I do with all my open source projects.

As always, if you find any bugs please let me know and I will correct them!

Your Homework:

Index Fragmentation Findings: Part 1, The Basics Brent Ozar (blog|twitter)

Index Defrag Script v4.1 Michelle Ufford (blog|twitter)