Identifying And Managing Index Fragmentation In SQL Server

Index fragmentation is one of those topics that many people are aware of, but not really sure exactly what it is or how to manage it.  Simply put, fragmentation is defined as any condition which causes more than the optimal amount of disk I/O to be performed when accessing a table.  Index fragmentation is an unavoidable outcome when working with any online transaction processing system.  The only way to manage it is to understand the various causes of fragmentation, know how to identify it and have a game plan for how to rebuild or reorganize the indexes in your database.  The information and steps outlined below should help you better understand index fragmentation as well as give you some insight as to how to mitigate fragmentation in your SQL Server environment.

The first thing to know are the various causes of index fragmentation.  The most common cause is due to page splits.  Page splits occur when there is no free space on a data page to allow for new inserts or updates.  This causes SQL Server to move a portion of the data from the current data page to a newly created data page.

Another common cause of index fragmentation is due to delete operations.  Performing ad hoc deletes can leave a data page in use but create empty rows within the data page.  After a while, these empty rows can begin to have a measurable impact on the overall performance of your SQL Server.

Identifying index fragmentation is relatively easy by using the  sys.dm_db_index_physical_stats Dynamic Management View (DMV).  The following script can be run against an individual database to identify the average fragmentation and average page space used as a percentage.  I have also JOINED to the sys.indexes DMV to allow for the table name to be included in the results.

Before running this script you’ll need to specify the database.  In the example above I am reviewing the index fragmentation of the AdventureWorks2012 database.

Another option you can tweak within the script is the mode.  There are three modes which can be chosen when using the sys.dm_db_index_physical_stats DMV.  Below is a brief description of each.

Detailed

Reads ALL data and index pages.  This mode will cause the entire index to be read into memory and can result in a significant performance hit.  Use caution when running a detailed scan.

Sampled

Reads only 1% of the pages when more than 10,000 pages exist.

Limited

Reads the parent level of the balanced tree.  This option neglects page density as it does not   read the leaf level pages.

The key metrics you want to review when running the above script are the Average Fragmentation In Percent and the Average Page Space Used In Percent.  These values will help you better determine where the highest level of fragmentation is occurring as well as the type of fragmentation.  Average fragmentation denotes logical fragmentation while average page space used denotes internal fragmentation.

Without going into too much detail, internal fragmentation occurs when data pages are not fully utilized.   The empty space in these pages is the fragmentation.  Logical fragmentation occurs when the logical order of the pages does not match the physical order.

The screenshot below shows a high level of fragmentation on several clustered and non clustered indexes within a database.

screenshot_index_fragmenation

As a basic rule, best practice suggests that any index with an average fragmentation value greater than 5% but less than 30% to be reorganized.  Any indexes with an average fragmentation value greater than 30% should be rebuilt.  

The following scripts will demonstrate rebuilding and reorganizing indexes on the SalesOrderHeader table of the AdventureWorks2012 database.

These script will allow you to either rebuild or reorganize all the indexes on a specific table within your database.  If you only desire to ALTER a single index, you can replace the ALL command with the name of the specific index.

After rebuilding or reorganizing your indexes it’s a good idea to re-run the initial script to verify the new fragmentation level as this will not always drop to zero.

Manually monitoring index fragmentation is a good start, but best practice is to automate the process of rebuilding and/or reorganizing indexes.  Though the schedule for doing so may differ from database to database, creating a SQL agent job to manage this is a good idea.  With a little modification to the script outlined at the beginning of this post you could even create a job to look for any indexes that fall outside the desired threshold and rebuild or reorganize as needed.