As demonstrated in my talk at the European PASS Conference 2010,
I am giving a brief summary of the gotchas in using filtered Indexes.
Filtered Indexes are extremely powerful when the entire concept is well thought out and some best practices are followed:
FI should reflect only a small subset of data
The demos have shown that the power of filtered indexes unleashes the better the smaller the filtered subset of data is. I would say that we have the best performance if the FI covers up to 20% of the rows in the table.
Always use exact FI filter expression in queries and SPs
This is very important especially for parametrerized queries and stored procedure. If we don’t supply the FI where-clause in the query, at compile time the engine does not know about the supplied parameter values and the filtered index won’t be used. In other words: Always include and do not parameterrize the FI where- expression in the queries.
Queries with a LIKE operator on FI key columns are likely not to use FI
Even if the result set is the same as for a query that has an equal operator on the filtered key column, which uses the FI, the like operator inhibits the use of the FI.
Connections must always have correct ANSI settings
The correct connection setting should read:
set ANSI_NULLS ON
set ANSI_PADDING ON
set ANSI_WARNINGS ON
set ARITHABORT ON
set CONCAT_NULL_YIELDS_NULL ON
set NUMERIC_ROUNDABORT OFF
set QUOTED_IDENTIFIER ON
The correct setting of these is important for both, the creation and usage of the FI. E.g., if we set ANSI_WARNINGS OFF for a connection that is to run a query that should use a FI, the FI will NOT be used. A query (or SP) that has an index hint to use a FI will fail with an error.
As I know that DBAs and developers love to dispute a lot, and assuming that there is only few developers who explicitly set their connection properties to what they should be in order to use filtered indexes, now here’s the DBAs ultimate chance: Just change the default ANSI connection settings at server level slightly to drive your developers crazy…
(Only kidding. NOT that I want to encourage anybody to do so!)
