Microsoft® SQL Server® 2012 Bible

1028

Part VII: Performance Tuning and Optimization

The following code redefi nes the indexes: one index keyed on ProductID and one keyed

on StartDate.

DROP INDEX Production.WorkOrder.IX_WorkOrder_ProductID;

CREATE INDEX IX_WorkOrder_ProductID

ON Production.WorkOrder (ProductID);

CREATE INDEX IX_WorkOrder_StartDate

ON Production.WorkOrder (StartDate);

With these indexes in place, this query fi lters by both key columns:

SELECT WorkOrderID, StartDate

FROM Production.WorkOrder

WHERE ProductID = 757

AND StartDate = '01/04/2006';

To use both indexes, SQL Server uses a merge join to request rows from each index seek

and then correlates the data to return the rows that meet both criteria, as shown in Figure

45-14. This is known as index-intersection. SQL Server makes use of both indexes via sepa-

rate operations to serve the query.

FIGURE 45-14

Filtering by two indexes adds a merge join into the mix.

Examining the performance stat in Table 45-1, multiple indexes have a query optimizer cost

of .12 and use four logical reads.

c45.indd 1028c45.indd 1028 7/31/2012 10:16:42 AM7/31/2012 10:16:42 AM

http://www.it-ebooks.info