Database Reference
In-Depth Information
■
the new index with the two included columns makes the
IDX_IndexIntersection_Col1
index redundant. We
will discuss this situation later in this chapter.
Note
CPU time and the number of reads are shown in Table
6-2
.
Table 6-2.
Index intersection vs. covering index
Number of Reads
CPU Time (ms)
Index Intersection
29
9 ms
Covering Index
18
1 ms
Even though the number of reads are not very different in both cases, the CPU time of the query with index
intersection is much higher than the query with covering index.
The design with multiple narrow nonclustered indexes, which lead to index intersection, can still help, especially
in the case of data warehouse systems where queries need to scan and aggregate a large amount of data. They are less
efficient, however, when compared to covering indexes. It is usually better to create a small set of wide indexes with
multiple columns included rather than a large number of narrow, perhaps single-column indexes.
While ideal indexes would cover the queries, it is not a requirement. A small number of Key Lookup operations
is perfectly acceptable. Ideally, SQL Server would perform a nonclustered Index Seek, filtering out rows even further
by evaluating other predicates against included columns from the index. This reduces the number of Key Lookups
required.
It is impossible to advise you about how many indexes per table you should create. Moreover, it is different for
systems with OLTP, Data Warehouse, or mixed workloads. In any case, that number fits into “It Depends” category.
In OLTP systems, where data is highly volatile, you should have the
minimally required
set of indexes. While it is
important to have enough indexes to provide sufficient query performance in the system, you must consider the data
modification overhead introduced by them. In some cases, it is preferable to live with suboptimal performance of
rarely executed queries, rather than living with the overhead during every data modification operation.
In Data Warehouse environments, you can create a large number of indexes and/or indexed views, especially
when data is relatively static and refreshed based on a given schedule. In some cases, you can achieve better update
performance by dropping indexes before and recreating them after update.
■
Note
We will cover indexed views in greater detail in Chapter 9, “Views.”
Working in mixed-workload environments is always a challenge. I tend to optimize them for OLTP activity,
which is usually customer facing and thus more critical. However, you always need to keep reporting/data warehouse
aspects in mind when dealing with such systems. It is not uncommon to design a set of tables to store aggregated data,
and also use them for reporting and analysis purposes.
Finally, remember to define indexes as unique whenever possible. Unique nonclustered indexes are more
compact because they do not store
row-id
on non-leaf levels. Moreover, uniqueness helps the Query Optimizer to
generate more efficient execution plans.
