In order to increase the chance that the query engine to employ the filtered indexes you created, make sure to include all the filtering column(s) of the index into its index key or included column list. Below example demonstrates why.
USE AdventureWorks2008
GO
-- Filtered Column in index key
DROP INDEX [Sales].[SalesOrderDetail].IX_1
GO
CREATE INDEX IX_1 ON [Sales].[SalesOrderDetail] (ProductID, SalesOrderID, OrderQty) WHERE OrderQty >= 2;
GO
SELECT ProductID, SalesOrderID FROM [Sales].[SalesOrderDetail] WHERE OrderQty >= 2 -- Use Filtered Index
SELECT ProductID, SalesOrderID FROM [Sales].[SalesOrderDetail] WHERE OrderQty >= 3 -- Use Filtered Index
-- Filtered Column NOT in index
DROP INDEX [Sales].[SalesOrderDetail].IX_1
GO
CREATE INDEX IX_1 ON [Sales].[SalesOrderDetail] (ProductID, SalesOrderID) WHERE OrderQty >= 2;
GO
SELECT ProductID, SalesOrderID FROM [Sales].[SalesOrderDetail] WHERE OrderQty >= 2 -- Use
SELECT ProductID, SalesOrderID FROM [Sales].[SalesOrderDetail] WHERE OrderQty >= 3 -- NOT use!
-- Filtered Column in INCLUDED
DROP INDEX [Sales].[SalesOrderDetail].IX_1
GO
CREATE INDEX IX_1 ON [Sales].[SalesOrderDetail] (ProductID, SalesOrderID) INCLUDE (OrderQty) WHERE OrderQty >= 2;
GO
SELECT ProductID, SalesOrderID FROM [Sales].[SalesOrderDetail] WHERE OrderQty >= 2 -- Use
SELECT ProductID, SalesOrderID FROM [Sales].[SalesOrderDetail] WHERE OrderQty >= 3 -- Use
By the way, the query optimizer won’t consider filtered indexes if you’re using local variables or parameterized SQL for the predicate that matches the filter. Dynamic SQL can help.
