Troubleshooting - Microsoft SQL Server 2008 R2 Administration - page 372

9. To help the optimizer, it has been decided to create a nonclustered index on the

ModifiedDate column by including the ProductID column as follows:
--Create an additional NONCLUSTERED INDEX
USE [Adventureworks2008R2]
'GO
CREATE NONCLUSTERED INDEX [IX_TransactionHistoryArchive_
ModifiedDate]
ON [Production].[TransactionHistoryArchive] ([ModifiedDate])
INCLUDE ([ProductID])
GO

10. As we have created the IX_TransactionHistoryArchive_ModifiedDate
index, let us check the execution plan of the query that is executed on step 4 without
executing as follows:
--Check the execution plan without executing the query
SET STATISTICS IO ON
GO
USE Adventureworks2008R2
GO
SET SHOWPLAN_ALL ON
GO
SELECT TransactionID, ProductID,TransactionDate,ModifiedDate FROM
Production.TransactionHistoryArchive
WHERE ProductID >710 AND
ModifiedDate >= '2007-01-01'
GO

11. Remember, we haven't executed the query, but we are able to obtain the query
execution plan using the SET SHOWPLAN_ALL statement, and the result is as follows:

12. The estimated show plan confirms the usage of the clustered index scan again,
which is a costly affair in case of returning large number of rows. So, the optimizer
has ignored the creation of a new nonclustered index on SARGable columns
ModifiedDate and ProductID .
13. Now, let us use the required hint FORCESEEK on the preceding query to see the
query execution difference that can reduce the resource usage.