



Tutorial: Diagnosing index fragmentation

The lessons in this tutorial explain how to use either the Application Profiling Wizard or Interactive SQL to determine if your database has unacceptable levels of index fragmentation, and if necessary, to fix 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: Identifying and fixing index fragmentation using the Application Profiling Wizard
Lesson 2: Identifying and fixing index fragmentation using Interactive SQL