Index Architecture and Behavior - SQL Server Query Performance Tuning - page 121

Now you're doing better than you were with the clustered index scan. A nice clean Index Seek operation takes
less than half the time to gather the data. The rest is spent in the Key Lookup operation. A Key Lookup operation used
to be referred to as a bookmark lookup.

■ Note You will learn more about key lookups in Chapter 11.

Although none of the columns in question would probably be selective enough on their own to make a decent
index, except possibly the birthdate column, together they provide enough selectivity for the optimizer to take
advantage of the index offered.
It is possible to attempt to force the query to use the first test index you created. If you drop the compound index,
create the original again, and then modify the query as follows by using a query hint to force the use of the original
Index architecture:

SELECT e.*
FROM HumanResources.Employee AS e WITH (INDEX (IX_Employee_Test))
WHERE e.BirthDate = '1984-12-05'
AND e.Gender = 'F'
AND e.MaritalStatus = 'M';

then the results and execution plan shown in Figure 8-12 , while similar, are not the same.

Table 'Employee'. Scan count 1, logical reads 414
CPU time = 0 ms, elapsed time = 103 ms.

Figure 8-12. Execution plan when the index is chosen with a query hint

You see the same index seek, but the number of reads has more than doubled, and the estimated costs within the
execution plan have changed. Although forcing the optimizer to choose an index is possible, it clearly isn't always an
optimal approach.
Another way to force a different behavior since SQL Server 2012 is the FORCESEEK query hint. FORCESEEK makes it
so the optimizer will choose only Index Seek operations. If the query were rewritten like this:

SELECT e.*
FROM HumanResources.Employee AS e WITH (FORCESEEK)
WHERE e.BirthDate = '1984-12-05'
AND e.Gender = 'F'
AND e.MaritalStatus = 'M';