Troubleshooting - Microsoft SQL Server 2008 R2 Administration - page 369

This completes the essential steps in designing a filtered indexes and statistics feature using
TSQL tasks.
How it works...
The filtered index is an optimized nonclustered index especially suited to cover queries from
a well-defined subset of data. The execution plan quality is more accurate than full table
statistics because they cover only the rows in the filtered index. Also, when the filtered index
contains only the frequently affected data with SELECT and UPDATE queries, the smaller size
of index reduces the cost of updating the statistics.
In this recipe, we begin with a table having a large number of rows and a frequently executed
query with the values such as:
ModifiedDate > '2006-01-01' and ModifiedDate < '2006-12-31'
AND Quantity > 1
Using the SET STATISTICS IO option and selecting Include Actual Execution Plan from
the query editor obtains a detailed execution plan and statistics. The feature of the execution
plan gives the hint of the missing index on that query and by creating a supportive index has
improved the execution of the same query. Similarly, we select a subset of data to be updated
for the ActualCost and ProductID columns and create supportive nonclustered index that
helps the query optimizer to use an effective plan with this index.
In the filtered indexes, the filter predicate allows a logic using operators such as IN , IS NOT ,
= , <> , !> , and so on. The filtered statistics are created along with filtered indexes that will
help the optimizer to use the same filter predicate to produce accurate results by taking the
sampling from the statistics.
Implementing table hints for advanced query
tuning performance
The table hints are similar to query hints that have been part of SQL Server since SQL Server
2000. The hints are useful to override query optimizer behavior during SELECT , INSERT ,
UPDATE , and DELETE execution tasks. Similarly, using the index hints in a TSQL query
can reduce the query optimizer from choosing a specified execute plan. There are certain
restrictions on using hints, such as both NOLOCK and HOLDLOCK cannot be used together.