Database Reference
In-Depth Information
Compare the preceding results of the query in Figure
13-16
with the earlier results in Figure
13-15
. You can see
that both internal and external fragmentation have been reduced efficiently. Here's an analysis of the output:
•
Internal fragmentation
: The table has 42 rows with an average row size (2,019.38 bytes) that
allows a maximum of four rows per page. If the rows are highly defragmented to reduce the
internal fragmentation to a minimum, then there should be 11 data pages in the table (or leaf
pages in the clustered index). You can observe the following in the preceding output:
•
Number of leaf (or data) pages
:
pagecount
= 11
•
Amount of information in a page
:
avg_page_space_used_in_percent
= 95.33 percent
•
External fragmentation
: A minimum of two extents is required to hold the 11 pages. For a
minimum of external fragmentation, there should not be any gap between the two extents,
and all pages should be physically arranged in the logical order of the index. The preceding
output illustrates the number of out-of-order pages =
avg_ fragmentation_in_percent
=
36.6 percent. Although this may not be a perfect level of fragmentation, being greater than
20 percent, this is adequate considering the size of the index. With fewer extents, aligned with
each other, access will be faster.
Rebuilding an index in SQL Server 2005 and greater will also compact the large object (LOB) pages. You can
choose not to by setting a value
LOB_COMPACTION = OFF
. If you aren't worried about storage but you are concerned
about how long your index reorganization is taking, this might be advisable to turn off.
When you use the
PAD_INDEX
setting while creating an index, it determines how much free space to leave on
the index intermediate pages, which can help you deal with page splits. This is taken into account during the index
rebuild, and the new pages will be set back to the original values you determined at the index creation unless you
specify otherwise. I've almost never seen this make a major difference on most systems. You'll need to test on your
system to determine whether it can help.
If you don't specify otherwise, the default behavior is to defragment all indexes across all partitions. If you want to
control the process, you just need to specify which partition you want to rebuild when.
As shown previously, the
ALTER INDEX REBUILD
technique effectively reduces fragmentation. You can also use it
to rebuild
all
the indexes of a table in one statement.
ALTER INDEX ALL ON dbo.Test1 REBUILD;
Although this is the most effective defragmentation technique, it does have some overhead and limitations:
•
Blocking
: Similar to the previous two index-rebuilding techniques,
ALTER INDEX REBUILD
introduces blocking in the system. It blocks all other queries trying to access the table (or any
index on the table). It can also be blocked by those queries.
•
Transaction rollback
: Since
ALTER INDEX REBUILD
is fully atomic in action, if it is stopped
before completion, then all the defragmentation actions performed up to that time are lost.
You can run
ALTER INDEX REBUILD
using the
ONLINE
keyword, which will reduce the locking
mechanisms, but it will increase the time involved in rebuilding the index.
Executing the ALTER INDEX REORGANIZE Statement
ALTER INDEX REORGANIZE
reduces the fragmentation of an index without rebuilding the index. It reduces external
fragmentation by rearranging the existing leaf pages of the index in the logical order of the index key. It compacts
the rows within the pages, reducing internal fragmentation, and discards the resultant empty pages. This technique
doesn't use any new pages for defragmentation.
To avoid the blocking overhead associated with
ALTER INDEX REBUILD
, this technique uses a nonatomic online
approach. As it proceeds through its steps, it requests a small number of locks for a short period. Once each step is
done, it releases the locks and proceeds to the next step. While trying to access a page, if it finds that the page is being
