SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

SQLDIY: Index Fragmentation Levels

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)

SQL Man of Mystery

Wes Brown is a PASS chapter leader and SQL Server MVP. He writes for SQL Server Central and maintains his blog at http://www.sqlserverio.com. Wes is Currently serving as a Senior Lead Consultant at Catapult Systems. Previous experiences include Product Manager for SQL Litespeed by Quest software and consultant to fortune 500 companies. He specializes in high availability, disaster recovery and very large database performance tuning. He is a frequent speaker at local user groups and SQLSaturdays.


Posted by peeyush.pandey24 on 10 September 2014

nice blog!!!

Leave a Comment

Please register or log in to leave a comment.