Index Fragmentation

The Index Fragmentation report shows the most fragmented indexes for your database.  You can use the Fragmented Indexes Advisor to defragment these indexes.

Fragmented indexes waste disk space, memory, increase backup size, and slow down index access times.  Defragmenting the largest indexes should be a priority if you are looking for optimal performance.

6 comments

  • DEFINITELY need a configuration for minimum number of pages in a table to bother with fragmentation check/display at all.

    Also, grid output should be sortable (this is an application-wide recommendation – EVERY grid should be sortable by clicking a column, with multi-column sorting a nice addition too).

  • Need configurable options for the various index frag checking parameters

    • Steve Stedman

      Thanks for the suggestion. I have added it to by request tracking system as ticket #112.
      I have been working on some other settings pages for the next beta. I should be able to add your request also.

      Enjoy the product.

  • I have a database that was created automagically by a 3rd party tool which has an index that is at a fragmentation level of 96.0%. I have the default settings for Database Health.
    The size of the index is 840 kb, used is 824kb, pages is 101, type is non-clustered, PK is no, and unicode is no.
    When I double click on the index, it pops up telling me all the things I can do and it clearly states “Fragmentation 96.0%” yet at the bottom text section it states “With fragmentation > 5 Standard Edition (64-bit). It is recommended to REORGANIZE this index”.

    I thought it would tell me to rebuild.

    • Steve Stedman

      With the standard edition of SQL Server the REBUILD would take the index offline. The REORGANIZE is recommended so that the index is not offline during the rebuild.

      Hope this helps clarify the recommendation.

      -Steve

Leave a Reply

Your email address will not be published. Required fields are marked *

To prove you are not a robot: * Time limit is exhausted. Please reload CAPTCHA.