No, not that
It’s not the F word.
Not like this embarrassing page that was somehow updated in 2017, which still tells you to reorg at 5% and rebuild at 30% fragmentation, like your data is still on a drive that looks like a record player and might still start making a clicking noise when it fails.
It also makes no mention of if this matters to data in memory.
You do have more than 3 GB of RAM in your 64bit server, right?
Fragmentation exists when indexes have pages in which the logical ordering, based on the key value, does not match the physical ordering inside the data file.
When’s the last time you saw a good demo of index fragmention causing a problem?
About the smartest person I’ve ever met, Joe Sack, once did a demo where he set Fill Factor to 5% — yes, that means the pages were 95% empty — and then scanned the entire index.
That’s empty space, not pages out of order, like the Microsoft docs page talks about.
Empty space. Caused by Fill Factor.
95% of it.
Let’s do the split
Some people will tell you that if have a lot of page splits, you should reduce fill factor.
This sounds reasonable. Sort of. With some empty space on the page, you’ll have a little more room available to insert values, which will reduce page splits.
The problem is that fill factor only gets applied when you rebuild an index, and you need to rebuild or reorg to apply that fill factor, and that lower fill factor is… Fragmentation!
If you chase that snake until the sun rises: in order to prevent logical fragmentation caused by page splits you need to introduce physical fragmentation with fill factor which is only respected when you rebuild or reorganize an index, which you do in order to remove logical fragmentation.
So why do these commands exist?
Well, I’d wager some of it is backwards compatibility. I still see questions about SQL Server 2000/2005 pop up on Stack Exchange.
There are some good reasons to Rebuild an index, like updating statistics.
Okay, maybe not that. You can always update statistics on their own.
But if you need to change something about an index (that hopefully isn’t Fill Factor), like:
- Add some Partitioning magic
- Add compression
- Change an index definition
- Change a Filegroup
- Try to fix corruption in a nonclustered index
An easy finger to point
Index fragmentation is an L1 support tech’s Mr. Boogedy.
I’ve heard it blamed for every conceivable database problem, and so the witch must be burned every night.
The thing is, most of those witches are just nice cat ladies who keep odd hours.
Thanks for reading!