1011
Chapter 45: Indexing Strategies
45
example, if several new Chapmans moved into the area and the Cha page 515 had to now
accommodate 20 additions, a simulated page split would take several steps:- Cut page 515 in half making two pages; call them 515a and 515b.
- Print out and tape the new Chapman to page 515a.
- Tape page 515b inside the back cover of the telephone book.
- Make a note on page 515a that the Cha listing continues on page 515b located at the
end of the book, and a note on page 515b that the listing continues on page 515a.
Page splits may cause several performance-related problems:
■ (^) The page split operation is expensive because it involves several steps and moving
data.
■ (^) If after the page split there still isn’t enough room, the page will be split again.
This can occur again and again based on certain circumstances.
■ (^) The data structure is left fragmented and can no longer be read in a single contigu-
ous pass.
■ (^) Page splits are also logged operations and can have a signifi cant impact on the
transaction log.
After the split, the page has more empty space. This means less data is read with every
page read, and less data is stored in the buffer pool per page along with additional disk
space required to store the data.
Index Selectivity
Another aspect of an indexing strategy is determining the selectivity of the index. An
index that is selective has more distinct index values. A primary key or unique index has
the highest possible selectivity, because every value in the constraint is defi ned as unique.
An index with only a few distinct values spread across a large table is less selective.
Indexes that are less selective may not be useful for searching. A column with three values
spread throughout the table is potentially a poor candidate for an index.
SQL Server uses its internal index statistics to track the selectivity of an index.
DBCC SHOW_STATISTICS reports the last date on which the statistics were updated and
the basic information about the index statistics, including the potential usefulness of
the statistic (see Figure 45-3). A low density indicates that the index is highly selective,
whereas a high density indicates low selectivity. The terms are the inverse of each other. A
high density may be less useful, as shown in this code sample:
USE AdventureWorks2012
DBCC SHOW_STATISTICS ('Person.Person',
IX_Person_LastName_FirstName_MiddleName);
c45.indd 1011c45.indd 1011 7/31/2012 10:16:38 AM7/31/2012 10:16:38 AM
http://www.it-ebooks.info