Tuning T-SQL - Professional SQL Server 2005 Performance Tuning - page 287

number of estimated rows in the execution plan. This plan estimates that 1 row should be returned
for each estimated time executed (1.2). The original plan estimates 12 rows with 12 executions for the
clustered index scan. Note that in reality, the number of reads is slightly higher and there is not much
duration difference.

The better way to solve this problem is to provide a covered index that contains the PurchaseOrder-
Number data so that a SEEK can be formed directly on the non-clustered index. Create such an index by
running this SQL in the AW database.

CREATE NONCLUSTERED INDEX [IX_SalesOrderHeader_CustomerID] ON [Sales]
.[SalesOrderHeader]
(

[CustomerID] ASC

)
INCLUDE ( [PurchaseOrderNumber]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE
= OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = ON, ONLINE
= OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

Running the query again shows a dramatic difference in I/O, as illustrated by Table 9-18. Page reads
are down to 16 pages. These pages are essentially the difference between reading the non-clustered
index (including the extra space for adding the PurchaseOrderNumber data) and the cost of loading the
clustered index pages via the bookmark lookup process. The plan is also much simpler now.

Index Seek(OBJECT:([IX_SalesOrderHeader_CustomerID] AS [SOH]),
SEEK:([SOH].[CustomerID]=CONVERT_IMPLICIT(int,[@1],0)),
WHERE:([SOH].[SalesOrderID]=[SalesOrderID]) ORDERED FORWARD)

Table 9-18: Comparison of Covered and Non-Covered Index on Retrieval

Method Tot Cost CPU Reads Duration

Without Covered Index 0.0393727 10 52 406ms

With Covered Index 0.0032952 10 16 114ms

This new covered index includes the PurchaseOrderNumber column in the index, but it is not part of
the indexed data. It essentially rides along for storage costs only. If the PurchaseOrderNumber column
would never be used as a searchable argument, then this is a good decision. If not, then consider cre-
ating a composite index instead. The idea here is if you have a performance problem due to a highly
repeated query, structure your indexes to support that retrieval to reduce the I/O on the clustered index.
However, there is a point of marginal return and it doesn't make sense to go the opposite direction and
have non-clustered indexes that include all the columns in the table. Follow these general guidelines
when creating covering indexes to remove bookmark style lookups:

❑ If the query is not a high-volume count query, then leave it alone. Don't index it. Take the hit.

❑ If adding the included or indexed columns significantly increases the index, then weigh size of

index vs. the size of the table itself. If relatively low ratio then add index.