Troubleshooting - Microsoft SQL Server 2008 R2 Administration - page 367

ON [Production].[TransactionHistoryArchive]
([Quantity],[ModifiedDate])
INCLUDE ([TransactionID],[ProductID],[ReferenceOrderID],[Transacti
onDate],[TransactionType],[ActualCost])
GO
*/
9. The improvement in the query execution cost is displayed as over 75% , which is a
benefit when the query is executed frequently.
10. As per the suggested NONCLUSTERED index information and from the query
text, let us replace [<Name of Missing Index, sysname,>] with [IX_
TransactionHistoryArchive_FilteredIdx_Quantity_ModifiedDate]
and execute in the query window.
11. Again, execute the query from step 4. The results are presented as follows:
--Post NONCLUSTERED suggested index creation results
--Execution Statistics
--(26499 row(s) affected)
--Table 'TransactionHistoryArchive'. Scan count 1, logical reads
333, physical reads 0, read-ahead reads 0, lob logical reads 0,
lob physical reads 0, lob read-ahead reads 0.
12. Continuing the discussion from step 3, proceed towards the creation of the new
NONCLUSTERED index for the update query. Initially, we execute the update query
without the supporting index as follows:
UPDATE Production.TransactionHistoryArchive SET
ActualCost=213.2999
WHERE ProductID >801 and ProductID<825 and
ModifiedDate>'2006-01-01' and ModifiedDate<'2006-12-31'