Tuning Indexes - Professional SQL Server 2005 Performance Tuning - page 395

9 1 88.8888888888889

9 2 0

10 0 98.772504091653

10 1 40

10 2 0

You should definitely try to resolve the fragmentation problem on these indexes. The following section
shows you how to handle this fragmentation.

Removing Fragmentation
Now that you know you have fragmented indexes, there are three options for cleaning them up. The first
is to drop and recreate each index. This is the most intrusive option. When an index is dropped, it is not
available for use. Moreover, the drop and create operations are atomic, so the table is locked while this is
happening and not available for use. The second option is to use the statement ALTER INDEX REORGANIZE ,
which became available with SQL Server 2005. This statement replaces DBCC INDEXDEFRAG .Thethird
option is to use the other new statement, ALTER INDEX REBUILD .Thisreplaces DBCC DBREINDEX .

If you have the luxury and time to take the table offline, you should use the first option, dropping and
recreating the indexes. If you need to keep the table online, you should use either of the ALTER INDEX
options. REORGANIZE is always an online operation and is interruptible without losing any progress, but
it isn't as effective as REBUILD . Index rebuilds default to an offline operation where the index is taken
offline for the duration of the operation. However, SQL Server 2005 introduced the ability to rebuild
online. It achieves this by duplicating the index and rebuilding the second copy before switching the
index requests to the newly rebuilt index and dropping the old one. This all happens transparently and
utilizes the row versioning feature in SQL Server 2005 to manage index updates during the rebuild, so
expect extra activity in TempDB.

You can run each option on the badly fragmented indexes to see how each one does.

You'll start with ALTER INDEX REORGANIZE . After running this command:

ALTER INDEX all ON people REORGANIZE

The index fragmentation now looks like this:

index_id index_level avg_fragmentation_in_percent
----------- ----------- ----------------------------
1 0 0.960960960960961

1 1 96.4285714285714

1 2 0

6 0 1.38740661686233

6 1 57.1428571428571

6 2 0

7 0 2.53968253968254

7 1 100

7 2 0

8 0 1.9639407598197

8 1 98.4375

8 2 0