What is "Index Fragmentation Percent" a percentage of?

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.

• What is that Percentage a percent of?

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):

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.

StackExchange Database Administrators Q#54731, answer score: 13