Designing and Tuning the Indexes - Pro SQL Server Internals - page 134

For the next step, let's look at the execution plan of the query that selects data from the table using three
predicates in the where clause. Each predicate can use an Index Seek operation on an individual index. The code for
doing this is shown in Listing 6-9 and the execution plan is shown in Figure 6-5 .
Listing 6-9. Multiple nonclustered indexes: Selecting data
select ID
from dbo.IndexIntersection
where Col1 = 42 and Col2 = 43 and Col3 = 44;
Figure 6-5. Multiple nonclustered indexes: Execution plan with index intersection
There are a couple things worth mentioning here. Even though there is another nonclustered index on Col1 , and
all indexes include an ID column, which we are selecting, SQL Server elects to use Key Lookup rather than perform
a third Index Seek operation. There are 20,971 rows in the table with Col1=42 , which makes Key Lookup the better
choice.
Another important factor is the cardinality estimation. Even though SQL Server correctly estimates cardinality
for both Index Seek operations, the estimation after the join operator is incorrect. SQL Server does not have any data
about the correlation of column values in the table, which can lead to cardinality estimation errors and, potentially,
suboptimal execution plans.
Let's add another covering index, which will include all three columns from the where clause, and run the query
from Listing 6-9 again. The code creates the index shown in Listing 6-10. The execution plan is shown in Figure 6-6 .
Listing 6-10. Multiple nonclustered indexes: Adding covering index
create nonclustered index IDX_IndexIntersection_Col3_Included
on dbo.IndexIntersection(Col3)
include (Col1, Col2)
Figure 6-6. Multiple nonclustered indexes: Execution plan with covering index