Rebuild Index

Table of Contents


Overview
Indexes and unique keys can be rebuilt. Since indexes do not become "fragmented", the only reason you would need to rebuild an index is to alter its partition size.

Partition Size

When an index is created, the engine will create a lookup tree containing the values from the fields which are specified in the index. One way to create this tree is to create one single large tree. The problem with this approach is that it causes lock contention, reduces parallelism opportunities, increases read/write size since the minimum index read size is 1 partition and takes a long time to traverse for index read operations.

The solution to these performance problems is partitioning. When an index is partitioned, the lookup trees will be striped across n-number of trees (where n is the partition count) using a quick hash of the lookup values.

Be default, the engine will use the globally configured index partition size but you can set a custom partition size per-index by specifying "Partitions" in the WITH section of the index creation or rebuilt statement.

Example
round-pushpin Rebuilds an index with the existing partition size.
REBUILD INDEX IX_Text on WordList:Word

round-pushpin Rebuilds an index with a new partition size.
REBUILD INDEX IX_Text on WordList:Word WITH (Partitions=5000)

round-pushpin Rebuilds a unique key with a new partition size.
REBUILD UniqueKey IX_Text on WordList:Word WITH (Partitions=5000)

See also
Partitions

Related