Database Reference
In-Depth Information
column prevented the optimizer from choosing the index on the column. You can use an
INDEX
hint to force the
optimizer to use the index on the
OrderID
column as follows:
SELECT *
FROM Purchasing.PurchaseOrderHeader AS poh WITH (INDEX (PK_PurchaseOrderHeader_PurchaseOrderID))
WHERE poh.PurchaseOrderID * 2 = 3400 ;
Note the relative cost of using the
INDEX
hint in comparison to not using the
INDEX
hint, as shown in Figure
18-14
.
Also, note the difference in the number of logical reads shown in the following
STATISTICS IO
outputs.
No hint (with the arithmetic operator on the
•
WHERE
clause column):
Table 'PurchaseOrderHeader'. Scan count
1
, logical reads 11
•
CPU time = 0 ms, elapsed time = 61 ms.
•
No hint (without the arithmetic operator on the
WHERE
clause column):
Table 'PurchaseOrderHeader'. Scan count 0, logical reads 2
CPU time = 0 ms, elapsed time = 27 ms.
•
INDEX
hint:
Table 'PurchaseOrderHeader'. Scan count
1
, logical reads 44
CPU time = 0 ms, elapsed time = 83 ms.
From the relative cost of execution plans and number of logical reads, it is evident that the query with the
INDEX
hint actually impaired the query performance. Even though it allowed the optimizer to use the index on
the
PurchaseOrderID
column, it did not allow the optimizer to determine the proper index-access mechanism.
Consequently, the optimizer used the index scan to access just one row. In comparison, avoiding the arithmetic
operator on the
WHERE
clause column and not using the
INDEX
hint allowed the optimizer not only to use the index on
the
PurchaseOrderID
column but also to determine the proper index access mechanism:
INDEX SEEK
.
Therefore, in general, let the optimizer choose the best indexing strategy for the query and don't override the
optimizer behavior using an
INDEX
hint. Also, not using
INDEX
hints allows the optimizer to decide the best indexing
strategy dynamically as the data changes over time. Figure
18-15
shows the difference between specifying index hints
and not specifying them.
