Troubleshooting - Microsoft SQL Server 2008 R2 Administration - page 365

How to do it...

The following steps are required in designing a filtered indexes and statistics feature using
TSQL tasks:

1. In this recipe, we will use the Production.TransactionHistoryArchive table

from the AdventureWorks2008R2 database.

2. The table consists of one clustered index on the TransactionID column and one

non-clustered index on the ProductID column, and holds more than 89,000 rows.

3. Assuming that we need to update the ActualCost column for a certain value on the

ProductID column for a specified year—as a part of a year-end process—creating
a full index on this table on the ActualCost and ProductID columns may be
considered as an uneconomical method.

4. On the SSMS from Query option, select Include Actual Execution Plan and execute

the following TSQL statement:
SET STATISTICS IO ON
GO
SELECT TransactionID,TransactionDate,ProductID,ReferenceOrderID,Tr
ansactionType,Quantity,ActualCost,ModifiedDate
FROM Production.TransactionHistoryArchive
WHERE ModifiedDate > '2006-01-01' and ModifiedDate < '2006-12-31'
AND Quantity > 1
SET STATISTICS IO OFF
GO

5. Running the preceding query with query statistics presents the following information

along with the execution plan:
--Execution Statistics
--(26499 row(s) affected)
--Table 'TransactionHistoryArchive'. Scan count 1, logical reads
622, physical reads 0, read-ahead reads 0, lob logical reads 0,
lob physical reads 0, lob read-ahead reads 0.