Search code examples
sqlsql-serversql-server-2008indexingreorganize

Reorganize index and level of defragmentation


I found this question here but no answer yet:

If an index is heavily fragmented (over 50 percent) will the alter index reorganize reduce the level of the fragmentation?


Solution

  • Yes it will - but only to a certain degree. Index reorg only shuffles around leaf-level pages of your index and will try to compact those - but it doesn't completely rebuild the index structure. So it can remove some fragmentation - but only on a limited scale.

    That's why as a rule of thumb, for fragmentation greater than about 30% (or you might want to pick a different threshold, like 25%, depending on your situtation), you should rebuild the index - not just reindex. Rebuild also updates the statistics which are vital for good query plans.