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:
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.
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.
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."
So it's how many pages out of the whole are out of order.