Database Reference
In-Depth Information
Chapter 6
Designing and Tuning the Indexes
It is impossible to define an indexing strategy that will work everywhere. Every system is unique and requires its
own indexing approach. However, there are several design considerations and guidelines that can be applied in
every system.
The same is true when we are optimizing existing systems. While optimization is an iterative process, which is
unique in every case, there is the set of techniques that can be used to detect inefficiencies in every database system.
In this chapter, we will cover a few important factors that you will need to keep in mind when designing new
indexes and optimizing existing systems.
Clustered Index Design Considerations
There are several design guidelines that help when choosing an efficient clustered index. Let's discuss them now
in detail.
Design Guidelines
Every time you change the value of a clustered index key, two things happen. First, SQL Server moves the row to a
different place in the clustered index page chain and the data file. Second, it updates the
row-id
, which is the clustered
index key, and is stored and needs to be updated in all nonclustered indexes. That can be expensive in terms of I/O,
especially in the case of batch updates. Moreover, it can increase the fragmentation of the clustered index and, in case
of row-id size increase, a nonclustered indexes. Thus it is better to have a
static
clustered index when key values do
not change.
All nonclustered indexes use a clustered index key as the
row-id
. A too wide clustered index key increases the size
of nonclustered index rows and requires more space to store them. As a result, SQL Server needs to process more data
pages during index or range scan operations, which makes the index less efficient.
Performance of singleton lookups are usually not affected by a wide
row-id
with one possible exception: when
a nonclustered index has not been defined as unique, it will store the
row-id
on non-leaf levels, which can lead to
extra intermediate levels in the index. Even though non-leaf index levels are usually cached in memory, it introduces
additional logical reads every time SQL Server traverses the nonclustered index B-Tree.
Finally, larger nonclustered indexes use more space in the buffer pool and introduce more overhead during index
maintenance. Obviously, it is impossible to provide a generic threshold value that defines the maximum acceptable
size of the key and that can be applied to any table. However, as the general rule, it is better to have a
narrow
clustered
index key with the index key as small as possible.
It is also beneficial to have the clustered index defined as
unique
. The reason this is important is not obvious.
Consider the scenario when a table does not have a unique clustered index and you want to run a query that uses
nonclustered index seek in the execution plan. In this case, if the
row-id
in the nonclustered index were not unique,
SQL Server would not know what clustered index row to choose during the key lookup operation.
