Database Reference
In-Depth Information
data. In addition, we can keep an eye on our disk space usage at a more
granular level.
Setting Up Index Maintenance
Finally we arrive at the last piece of the index puzzle: maintenance. We've
discussed why you need to perform maintenance but haven't said very
much about what it is and when you need to do it. As mentioned before,
index maintenance is primarily in the realm of the DBA, but if you can pro-
vide guidance about the frequency and type of maintenance that is likely
to be needed, the DBA will be grateful. Here are the things you need to
know.
Indexes, because of the insertion and deletion of data, can become
very fragmented. That is, to perform as fast as possible, SQL Server exe-
cutes these changes in a very efficient manner. Whenever changes are
made that require index pages to be split or allocated, SQL Server grabs
the quickest available page and allocates it to the index in question. Over
time, this means that the index information is scattered throughout the
database, in terms of physical location on disk. This in turn has a negative
effect on performance. When we refer to performing maintenance on an
index, we are referring to fixing this problem.
Rebuilds versus Reorganization
There are two ways to remedy the fragmentation that occurs with indexes:
rebuilding the index and reorganizing the index. Rebuilding the index
means literally that—dropping and re-creating the index. It can be a dis-
ruptive process, because it literally drops the existing index and re-creates
it, allocating all new, contiguous pages to the new version of the index. This
process, however, completely removes fragmentation. In Enterprise
Edition, you can execute this online, lowering the impact on users who
may be trying to access the table while the index is being rebuilt. However,
it can cause the rebuild to take a very long time, so when possible, you
should execute it offline.
Alternatively, you can reorganize an index. Reorganization simply re-
arranges the information on the index pages, using only the pages already
allocated to the index. Although this doesn't solve the problem of the pages
being discontinuous on the disk, it does speed the scanning of the nodes of
the index, because they are in order even if they aren't physically next to
Search WWH ::




Custom Search