Tuning with Execution Plans - Microsoft SQL Server 2012 Performance Tuning - page 132

9. As per our guidance, SQL Server optimizer has used clustered index but it is not able

to make seek on clustered index, so it is showing scan on the clustered index.

10. Clustered Index Scan is shown in the previous screenshot, which is not good for
returning only a few records of date. So it would be interesting to know which one
is better, Key Lookup or clustered index scan. Let us compare the load by keeping
execution plan (press Ctrl + M ) on in the following query and also keeping SET
STATISTICS IO ON to measure IO load:
SET STATISTICS IO ON
GO

SELECT
ModifiedDate,
SalesOrderID,
SalesOrderDetailID,
ProductID,
UnitPrice
FROM SalesOrdDetailDemo
WHERE ModifiedDate='2005-07-01 00:00:00.000'
GO

SELECT
ModifiedDate,
SalesOrderID,
SalesOrderDetailID,
ProductID,
UnitPrice
FROM SalesOrdDetailDemo WITH(INDEX=idx_SalesDetail_SalesOrderlID)
WHERE ModifiedDate='2005-07-01 00:00:00.000'
GO

SELECT