question

jdonaldconrad avatar image
jdonaldconrad asked

What does percent of index fragmentation literally mean?

What does this phrase actually mean? If I have a table with 1,000 rows in it and a non-clustered index is reported to be 50% fragmented, does this mean the indexes are on 500 separate pages? Or does it mean that half of the times that I go to retrieve data I must go to a second page in order to find the data?
indexfragmentation
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
TimothyAWiseman avatar image
TimothyAWiseman answered
It could mean slightly different things because there are different types of fragmentation that this deals with. For one good reference look at: [Notes - SQL Server Index Fragmentation, Types and Solutions][1] and [Stop Worrying About SQL Server Fragmentation][2] (The second one includes several plugs for other products, but it is still a good article.). The short, perhaps too simplified version, is that there is fragmentation whenever the next thing SQL Server would find in the index is not immediately the next record that should appear in the index. One significant note is that this is tracked sepearately from the fragmentation of the physical files on the disk. Removing fragmentation from the index will be of very little help if the file itself is horribly fragmented. [1]: http://blogs.msdn.com/b/pamitt/archive/2010/12/23/notes-sql-server-index-fragmentation-types-and-solutions.aspx [2]: http://www.brentozar.com/archive/2012/08/sql-server-index-fragmentation/
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.