Index Fragmentation - SQL Server Query Performance Tuning - page 248

Fragmentation Resolutions
You can resolve fragmentation in an index by rearranging the index rows and pages so that their physical and logical
orders match. To reduce external fragmentation, you can physically reorder the leaf pages of the index to follow the
logical order of the index. You achieve this through the following techniques:

• Dropping and re-creating the index

DROP_EXISTING = ON clause • Re-creating the index with the

ALTER INDEX REBUILD statement on the index • Executing the

ALTER INDEX REORGANIZE statement on the index

• Executing the

Dropping and Re-creating the Index
One of the apparently easiest ways to remove fragmentation in an index is to drop the index and then re-create it.
Dropping and re-creating the index reduces fragmentation the most since it allows SQL Server to use completely new
pages for the index and populate them appropriately with the existing data. This avoids both internal and external
fragmentation. Unfortunately, this method has a large number of serious shortcomings.

• Blocking : This technique of defragmentation adds a high amount of overhead on the system, and
it causes blocking. Dropping and re-creating the index blocks all other requests on the table (or
on any other index on the table). It can also be blocked by other requests against the table.

• Missing index : With the index dropped, and possibly being blocked and waiting to be re-created,
queries against the table will not have the index available for use. This can lead to the poor
performance that the index was intended to remedy.

• Nonclustered indexes : If the index being dropped is a clustered index, then all the nonclustered
indexes on the table have to be rebuilt after the cluster is dropped. They then have to be rebuilt
again after the cluster is re-created. This leads to further blocking and other problems such as
statement recompiles (covered in detail in Chapter 17).

• Unique constraints : Indexes that are used to define a primary key or a unique constraint
cannot be removed using the DROP INDEX statement. Also, both unique constraints and
primary keys can be referred to by foreign key constraints. Prior to dropping the primary key,
all foreign keys that reference the primary key would have to be removed first. Although this is
possible, this is a risky and time-consuming method for defragmenting an index.
It is possible to use the ONLINE option for dropping a clustered index, which means the index is still readable while it
is being dropped, but that saves you only from the foregoing blocking issue. For all these reasons, dropping and re-creating
the index is not a recommended technique for a production database, especially at anything outside off-peak times.

Re-creating the Index with the DROP_EXISTING Clause
To avoid the overhead of rebuilding the nonclustered indexes twice while rebuilding a clustered index, use the
DROPEXISTING clause of the CREATE INDEX statement. This re-creates the clustered index in one atomic step, avoiding
re-creating the nonclustered indexes since the clustered index key values used by the row locators remain the same.
To rebuild a clustered key in one atomic step using the DROP_EXISTING clause, execute the CREATE INDEX statement
as follows:

CREATE UNIQUE CLUSTERED INDEX FirstIndex
ON dbo.Test1(C1)
WITH (DROP_EXISTING = ON);