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

How Non-Clustered Index Key Columns Are Stored

Kendra Little walks through page-level details on a non-clustered index: Just like in the root page and the intermediate pages, the FirstName and RowID columns are present. Also in the leaf: CharCol, our included column appears! It was not in any of the other levels we inspected, because included columns only exist in the leaf of a […]

Read More

Using DMVs To Plan Out Your Indexes

Eric Blinn explains how to use two particular DMVs to see which index changes you might want to make: Missing Indexes This group of DMVs records every scan and large key lookups.  When the optimizer declares that there isn’t an index to support a query request it generally performs a scan.  When this happens a […]

Read More

Categories

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