So what exactly is index fragmentation? Index fragmentation actually comes in two different forms: external fragmentation and internal fragmentation. Each of these two forms of fragmentation basically is the inefficient use of pages within an index. This inefficient use may be because the logical order of the pages are wrong (external fragmentation) or because the amount of data stored within each page is less than the data page can contain (internal fragmentation). Whichever type of fragmentation occurs in your index, you could face performance issues with your queries because of the fragmentation.
External Fragmentation
External fragmentation occurs when an index leaf page is not in logical order. When an index is created, the index keys are placed in a logical order on a set of index pages. As new data is inserted into the index, it is possible for the new keys to be inserted in between existing keys. This may cause new index pages to be created to accommodate any existing keys that were moved so that the new keys can be inserted in correct order. These new index pages usually will not be physically adjacent to the pages the moved keys were originally stored in. It is this process of creating new pages that causes the index pages to be out of logical order.
The following example will explain this concept a little clearer than actual words. Assume this is the existing structure on an index on your table before any additional data inserts.
An INSERT statement adds new data to the index. In this case we will add a 5. The INSERT will cause a new page to be created and the 7 and 8 to be moved to the new page in order to make room for the 5 on the original page. This creation will cause the index pages to be out of logical order.
In cases of queries that have specific searches or that return unordered result sets, the index pages being out of order do not pose a problem. For queries that return ordered result sets, extra processing is needed to search the index pages that are not in order. An example of an ordered result set would be a query that is returning everything from 4 to 10. This query would have to complete an extra page switch in order to return the 7 and 8. While one extra page switch is nothing in the long run, imagine this condition on a very large table with hundreds of page out of order.
Internal Fragmentation
Internal fragmentation occurs when the index pages are not being used to their maximum volume. While this may be an advantage on an application with heavy data inserts, setting a fill factor causes space to be left on index pages, severe internal fragmentation can lead to increased index size and cause additional reads to be performed to return needed data. These extra reads can lead to degradation in query performance.
How to Determine If an Index is Fragmented?
SQL Server provides a database command, DBCC SHOWCONTIG, to use to determine if a particular table or index has fragmentation.
DBCC SHOWCONTIG
Database console command that displays fragmentation information for the data and indexes of the specified table.
Permissions default to members of the sysadmin server role, the db_owner and db_ddladmin database roles and the table owner and are not transferable.
Syntax (SQL Server 2000)
DBCC SHOWCONTIG
[ ( { table_name | table_id| view_name | view_id }
[ , index_name | index_id ]
)
]
[ WITH { ALL_INDEXES
| FAST [ , ALL_INDEXES ]
| TABLERESULTS [ , { ALL_INDEXES } ]
[ , { FAST | ALL_LEVELS } ]
}
]