Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

So I am familiar with the concept of index fragmentation and how to fix it but I'm not really sure of one specific item:

When you look at index fragmentation, it's always reported in terms of percentage. But what is that percentage a percent of?

share|improve this question
add comment

2 Answers

up vote 5 down vote accepted

It's going to be logical fragmentation for indexes, and extent fragmentation for heaps. The BOL reference on sys.dm_db_index_physical_stats actually gives pretty good information on the topic:

Logical Fragmentation

This is the percentage of out-of-order pages in the leaf pages of an index. An out-of-order page is a page for which the next physical page allocated to the index is not the page pointed to by the next-page pointer in the current leaf page.

Extent Fragmentation

This is the percentage of out-of-order extents in the leaf pages of a heap. An out-of-order extent is one for which the extent that contains the current page for a heap is not physically the next extent after the extent that contains the previous page.

The value for avg_fragmentation_in_percent should be as close to zero as possible for maximum performance. However, values from 0 percent through 10 percent may be acceptable. All methods of reducing fragmentation, such as rebuilding, reorganizing, or re-creating, can be used to reduce these values. For more information about how to analyze the degree of fragmentation in an index, see Reorganize and Rebuild Indexes.

Think about an index, as highlighted above it's the "percentage of out-of-order pages in the leaf pages". An index page is going to be a doubly-linked list. In other words, each page points to the next page and the previous page in it's respective index level. Here's a picture on what a doubly linked list looks like (for illustrative purposes):

enter image description here

Note: the above image was just randomly pulled from the internet, it's a simple representation. But it does lack A's next pointer, and F's previous pointer. I didn't want any confusion that it's exactly how a leaf page in a SQL Server index would look like.

This structure is going to represent the logical flow of the data. In other words, what page comes next regardless of what the physical reality is. That logical fragmentation comes into play when the actual physical next page isn't what the next page is pointed to by the current leaf page.

Given the above definition, say you have four index pages. 1 out of 4 of those index pages next page pointer is not the physical page next to it, but the other 3 do have that physical nature. In that case, your percent of fragmentation would be 25%. Of course, that was a simple example and in reality you wouldn't care about an index less than thousands of pages. But hopefully that makes it a little clearer exactly what that number represents.

Here's a few good reads on Clustered Index Structures, Nonclustered Index Structures, and Heap Structures.

share|improve this answer
add comment

I found the answer elsewhere on the Internet, courtesy of Grant Fritchey.

"When you look at the fragmentation percentage, you're looking at external fragmentation. It means the percentage of pages that are out of order showing an inneficient storage mechanism."

http://ask.sqlservercentral.com/questions/6505/what-is-index-fragmentation-actually.html

So it's how many pages out of the whole are out of order.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.