Index Fragmentation in SQL Server 2005

Index fragmentation is a phenomenon where the index contents are scattered. Normally the contents are in contiguous fashion which helps in fast retrieval of the underlying data. When the indexes are fragmented the data access becomes time consuming because of the scattered data that needs to be searched and read. Fragmentation occurs as data is modified. The following are the two types of Index fragmentation:

1. Internal fragmentation
2. External fragmentation

Internal fragmentation: This happens when space is available within your index page i.e. when the index pages have not been filled as full as possible. Due to internal fragmentation the index is taking up more space than it needs to. Thus when scanning the index it results in more read operations. Internal fragmentation also happens due to specifying a low value of fill factor (which determines the % of space to be filled in a leaf level page). This is also caused by rows that are removed by DELETE statements or when pages are split and only filled to about half. Empty space on pages means there are less rows per page, which in turn means more page reads.

 External Fragmentation: External fragmentation occurs when the pages are not contiguous on the index. If the pages in a book are NOT ordered in a logical way (page 1, then page 2, then page 3 and so on) causing you to go back and forward to compound the information and make sense of the reading. External fragmentation happens when there are frequent UPDATES and INSERTS in a table having small amount of free space in the index page. Since the page is already full or only has less free space left and if it is not able to accommodate the new row inserted or updated, as a result Page split happens in order to allocate the new row. Due to page split, original page will be split such that half the rows are left on the original page and the other half is moved to the new page. Mostly the new page is not contiguous to the page being split. Page split is an expensive operation and should always be avoided. How to determine fragmentation ? The following query will give the fragmentation information of a particular table named person.address in adventureworks database. Please modify the query to replace the database name and table name according to your requirements.

1 2 3

SELECT CAST(DB_NAME(database_id) AS VARCHAR(20)) AS [DATABASE Name], CAST(OBJECT_NAME(OBJECT_ID) AS VARCHAR(20)) AS [TABLE NAME], Index_id, Index_type_desc, Avg_fragmentation_in_percent, Avg_page_space_used_in_percent FROM sys.dm_db_index_physical_stats(DB_ID('AdventureWorks'),OBJECT_ID('person.address'),NULL,NULL,'Detailed')

If you wish to identify the fragmentation information for the tables in a particular database please use the below query. I am using it to find the fragmentation in Adventureworks database.

1 2 3

SELECT CAST(DB_NAME(database_id) AS VARCHAR(20)) AS [DATABASE Name], CAST(OBJECT_NAME(OBJECT_ID) AS VARCHAR(20)) AS [TABLE NAME], Index_id, Index_type_desc, Avg_fragmentation_in_percent, Avg_page_space_used_in_percent FROM sys.dm_db_index_physical_stats(DB_ID('AdventureWorks'),NULL,NULL,NULL,'Detailed')

Take a look at the output of the following columns in the above query: Avg_fragmentation_in_percent: If the value is >5 and <30 you need to REORGANIZE the index using ALTER index REORGANIZE command. If the value is >30 you need to REBUILD the indexes using ALTER index REBUILD command. Avg_page_space_used_in_percent: This value represents the amount of page space used in an index. If the value is <75% and >60% we need to REORGANIZE the indexes else REBUILD the indexes. Defragmenting the Indexes: We need to either use ALTER INDEX REBUILD or ALTER INDEX REORGANIZE commands to remove the fragmentation from the indexes. Generally its advisable to schedule a job to do this operations in OFF-Production hours as they consume lots of resources.