To Defrag or Not To Defrag?

You might wonder whether there is any harm in initiating a defragmentation operation when little or no fragmentation exists; after all, the amount of page swapping seems to be proportional to the number of out-of-order pages. In most cases, the defragmentation won't swap pages when no fragmentation exists. However, a pathological situation, in which a small amount of fragmentation can cause an enormous amount of work, can occur.

Related: Does Your Database Need a Defrag?

Imagine an index of thousands of pages that are all completely in order—except for the last one in logical sequence. So, the first data in order (say, for January 1, 2000) is on page 101, the next data (for January 2, 2000) is on page 102, the next on 103, and so forth. But the last logical page of data (for January 31, 2006) is stored on page 100. If there were 1000 pages in the table, the fragmentation value reported would be only 0.1 percent because only 1 out of 1000 pages is out of order. However, if you decided to reorganize this index, the defragmentation algorithm would switch page 100 with 101, then switch 101 with 102, then 102 with 103, in an effort to get the data for the last date in the table to be at the last page. Only one page is out of order, but that one page requires 1000 page swaps.

The algorithms for both SQL Server 2000 and 2005 have this limitation, so I strongly suggest that you don't defragment your data unless you know that the data is very fragmented and you've determined that you need unfragmented data for maximum performance.