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

Figure 8-15. Execution plan for query against inner columns
Both queries return 31 rows from the same table, but the number of reads jumped from 74 to 180. You begin to
see the difference between the Index Seek operation in Figure 8-14 and the Index Scan operation in Figure 8-15 .
Also note that because it had to perform a scan, the optimizer indicated that there might be a possible index to help
improve the performance of the query.
Missing index information is useful as a pointer to the potential for a new or better index on a table, but don't
assume it's always correct. You can right-click the place where the missing index information is and select Missing
Index Details from the context menu. That will open a new query window with the details of the index laid out, ready
for creation. If you do decide to test that index, make sure you rename it from the default name.
Finally, to see the order of the index really shine, change the query to this:
SELECT a.AddressID,
a.City,
a.PostalCode
FROM Person.Address AS a
WHERE a.City = 'Gloucestershire'
AND a.PostalCode = 'GL7 1RY';
Executing this query will return the same number of rows as the previous queries, resulting in the following:
Table 'Address'. Scan count 1, logical reads 2
CPU time = 15 ms, elapsed time = 0 ms.
The execution plan is visible in Figure 8-16 .