Tutorial: Diagnosing index fragmentation

Use the lessons in this tutorial to learn how to use the Application Profiling Wizard to determine if your database has unacceptable levels of index fragmentation.

When an index is created, table data is read and values for the index are recorded on index pages following a logical order. As data changes in the table, new index values can be inserted between existing values. To maintain the logical order of index values, the database server may need to create new index pages to accommodate existing values that are moved. The new pages are not usually adjacent to the pages on which the values were originally stored. This cumulative degradation in the order of index pages is called index fragmentation.

Commonly executed queries taking longer to perform on tables where large blocks of rows are continuously being inserted, updated, and deleted is a symptom of index fragmentation.


Lesson 1: Find and fix index fragmentation using the Application Profiling Wizard
Identifying and fixing index fragmentation using SQL