I made them up. Yup.
I'm talking about the guidance which is:
-
if an index has less than 1000 pages and is in memory, don't bother removing fragmentation
-
if the index has:
-
less than 5% logical fragmentation, don't do anything
-
between 5% and 30% logical fragmentation, reorganize it (using DBCC INDEXDEFRAG or ALTER INDEX … REORGANIZE)
-
more than 30% logical fragmentation, rebuild it (using DBCC DBREINDEX or ALTER INDEX … REBUILD)
-
These numbers are made up. They can and will vary for you, but they're a good starting point to work from.
There's been some discussion since PASS, when I confessed publicly on Twitter (during Grant Fritchey's session) to making them up, about whether I really said that etc etc. Yes – I really did make them up.
Back in 1999/2000 when I wrote DBCC INDEXDEFRAG and DBCC SHOWCONTIG for SQL Server 2000, customers wanted *some* guidance on what the thresholds should be where they should care about fragmentation or not, and how to remove it. We had to put *something* into Books Online (my favorite "it depends!" wouldn't have been too helpful), so I talked to some customers, inside and outside Microsoft, and chose these numbers as most appropriate at the time.
They're not set in stone – they're a big generalization, and there are a ton of other factors that may affect your choice of threshold and fragmentation removal method (e.g. recovery model, high-availability technologies in use, log backup schedule, query workload, disk space, buffer pool memory, and so on). I wish Microsoft would update the old whitepaper on fragmentation – they keep promising me they'll get around to it.
In the meantime, take those numbers with a pinch of salt and don't treat them as absolute.
9 Responses to Where do the Books Online index fragmentation thresholds come from?
Paul,
Although I don’t discount your numbers, I have heard different numbers from Microsoft engineers in the recent past. Two engineers have said not to bother with rebuilding indexes until fragmentation hits 75% as anything below that will not have a performance benefit if the index is rebuilt. Both engineers stated the same as you in regards to ignoring any indexes with less than 1000 pages.
~Tara
Hey Tara – that’s just BS. It totally depends. I’d love to know why they were so I can contact them using my Microsoft email account. Cheers.
Yeah I didn’t totally believe them, so I changed my index rebuilds to start at 50% fragmentation.
I’ll take your random numbers over my calculated ones any day. ;-)
Does the size of an index influence your thoughts on those recommendations? In my experience the larger the index the lower the threashold should be. I am just curious to hear your thoughts.
I understand your point. Let’s say you have any index that is 26 gigabytes that has 30% fragmentation compared to a 2 megabyte index with 65% fragmentation the much larger index will put more stress on the disk systems and should (in my thinking) lower the threshold. How do you tell the distribution of the fragments in the index?
It’s a bit hard to say – if the fragmentation is spread through the index, then a higher number could be fine. If the fragmentation is limited to a small section of a large index, but that section is the one being scanned, then a lower number should be the threshold. That gets a bit tricky to explain to people so I tend not to go into details. Sounds like something for my perf benchmarking series though!
Indeed. There’s no easy way to tell the distribution. Various commercial tools will give you a ‘map’ like a disk defrag tool does, but there’s no T-SQL on the net to do it AFAIK. If you know the DML pattern on your table you should be able to work it out with a bit of thought.
[...] issue with these thresholds. For more background info on these thresholds I strongly advise reading Paul Randal’s post on where these guidelines came [...]