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?
It's going to be logical fragmentation for indexes, and extent fragmentation for heaps. The BOL reference on
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
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.