Filtered Indexes Are Tricky

Kevin Eckart investigates filtered indexes not being used:

This warning is telling me that Parameterization is to blame for the filtered index not being used. From here, I see 3 options.

  1. Remove the parameters and use literals. (not practical)

  2. Use Dynamic SQL

  3. Use OPTION(RECOMPILE) at the bottom of the query.

This is the classic issue with filtered indexes:  you expect them to be used, but when you check the plan, they aren’t.

Related Posts

Minimal Logging into Empty Clustered Indexes

Paul White explains how to perform minimal logging when using the INSERT..SELECT pattern to insert into an empty table with a clustered index: The summary top row suggests that all inserts to an empty clustered index will be minimally logged as long as TABLOCK and ORDER hints are specified. The TABLOCK hint is required to enable the RowSetBulk facility as used for heap table bulk loads. An ORDER hint […]

Read More

Compress Those Indexes

Pamela Mooney reminds us that if you’re going to compress your heap or clustered index, remember those non-clustered indexes as well: We compress to gain space, correct? So why sacrifice it to our indexes? Here is a script (complete with demo databases and tables) that will find the culprits and fix them for you.  Put […]

Read More

Categories

January 2016
MTWTFSS
« Dec Feb »
 123
45678910
11121314151617
18192021222324
25262728293031