Index Fragmentation - SQL Server Query Performance Tuning - page 252

used, it skips that page and never returns to the page again. This allows other queries to run on the table along with
the ALTER INDEX REORGANIZE operation. Also, if this operation is stopped intermediately, then all the defragmentation
steps performed up to then are preserved.
Since ALTER INDEX REORGANIZE doesn't use any new pages to reorder the index and it skips the locked pages, the
amount of defragmentation provided by this approach is usually less than that of ALTER INDEX REBUILD . To observe
the relative effectiveness of ALTER INDEX REORGANIZE compared to ALTER INDEX REBUILD , rebuild the test table used
in the previous section on ALTER INDEX REBUILD .
Now, to reduce the fragmentation of the clustered index, use ALTER INDEX REORGANIZE as follows:

ALTER INDEX i1 ON dbo.Test1 REORGANIZE;

Figure 13-17 shows the resultant output from sys.dm_db_index_physical_stats .

Figure 13-17. Results of ALTER INDEX REORGANIZE

From the output, you can see that ALTER INDEX REORGANIZE doesn't reduce fragmentation as effectively as
ALTER INDEX REBUILD , as shown in the previous section. For a highly fragmented index, the ALTER INDEX REORGANIZE
operation can take much longer than rebuilding the index. Also, if an index spans multiple files, ALTER INDEX
REORGANIZE doesn't migrate pages between the files. However, the main benefit of using ALTER INDEX REORGANIZE is
that it allows other queries to access the table (or the indexes) simultaneously.
Table 13-1 summarizes the characteristics of these four defragmentation techniques.

Table 13-1. Characteristics of Defragmentation Techniques

ALTER
INDEX REBUILD

ALTER INDEX
REORGANIZE

Characteristics/Issues Drop and

Create Index

Create Index with
DROP_ EXISTING

Rebuild nonclustered
indexes on

Twice No No No

clustered index
fragmentation
Missing indexes Yes No No No

Defragment index
with constraints

Highly complex Moderately

complex

Easy Easy

Defragment multiple
indexes together

No No Yes Yes

Concurrency with others Low Low Medium, depending

on concurrent
user activity

High

Intermediate cancellation Dangerous

with no transaction

Progress lost Progress lost Progress

preserved

Degree of defragmentation High High High Moderate to low

Apply new fill factor Yes Yes Yes No

Statistics are updated Yes Yes Yes No