My Dear Friend™ Sean recently wrote a post talking about how people are doing index maintenance wrong. I’m going to go a step further and talk about how the method your index maintenance scripts use to evaluate fragmentation is wrong.
If you look at how the script you use to decide whether or not you’re going to rebuild indexes works, and this goes for maintenance plans, too (I ran PROFILER LONG LIVE PROFILER GO TEAM PROFILER to confirm the query), you’ll see they run a query against dm_db_index_physical_stats.
All of the queries use the column avg_fragmentation_in_percent to measure if your index needs to be rebuilt. The docs (linked above) for that column have this to say:
It’s measuring logical fragmentation. Logical fragmentation is when pages are out of order.
If you’re on decent disks, even on a SAN, or if you have a good chunk of memory, you’ll learn from Sean’s Great Post© that this is far from the worst fate to befall your indexes. If you keep up your stats maintenance, things will be okay for you.
If you’re the kind of person who cares about various caches on your server, like the buffer pool or the plan cache, then you’d wanna measure something totally different. You’d wanna measure how much free space you have on each page, because having a bunch of empty space on each page means your data will take up more space in memory when you read it in there from disk.
You could do that with the column avg_page_space_used_in_percent.
Your favorite index maintenance solution will do you a favor and only run dm_db_index_physical_stats in LIMITED mode by default. That’s because taking deeper measurements can be rough on a server with a lot of data on it, and heck, even limited can run for a long time.
But if I were going to make the decision to rebuild an index, this is the measurement I’d want to use. Because all that unused space can be wasteful.
The thing is, there’s not a great correlation between avg_fragmentation_in_percent being high, and avg_page_space_used_in_percent.
When looking at fragmentation in my copy of the Stack Overflow 2013 database:
Both of those tables are fragmented enough to get attention from a maintenance solution, but rebuilding only really helps the Posts table, even though we rebuilt both.
On the comments table, avg_page_space_used_in_percent goes down a tiny bit, and Posts gets better by about 10%.
The page count for Comments stays the same, but it goes down by about 500k for Posts.
This part I’m cool with. I’d love to read 500k less pages, if I were scanning the entire table.
But I also really don’t wanna be scanning the entire table outside of reporting or data warehouse-ish queries.
If we’re talking OLTP, avoiding scanning large tables is usually worthwhile, and to do that we create nonclustered indexes that help our queries find data effectively, and write queries with clear predicates that promote the efficient use of indexes.
Think About Your Maintenance Settings
They’re probably at the default of 5% and 30% for reorg and rebuild thresholds. Not only are those absurdly low, but they’re not even measuring the right kind of fragmentation. Even at 84% “fragmentation”, we had 75% full pages.
That’s not perfect, but it’s hardly a disaster.
Heck, you’ve probably been memed into setting fill factor lower than that to avoid fragmentation.
Worse, you’re probably looking at every table >1000 pages, which is about 8 MB.
If you have trouble reading and keeping 8 MB tables in memory, maybe it’s time to go shopping.
Thanks for reading!