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.