The index works in your hand-written query, then disappears when the application supplies a parameter. A filtered index is ignored when SQL Server cannot prove that its rows cover every value a reusable plan must handle. Start with that safety requirement before trying to force the index.
Separate Index Eligibility From Index Cost
A filtered index contains only rows satisfying its predicate. If the query asks for IsActive equal to one, an index filtered on that same condition can supply the required rows. If a reusable query accepts either zero or one through a parameter, the same index cannot cover both cases.
I check that logical guarantee before checking the index's size. A small and selective index is still unusable if it can miss rows for a supported parameter value. The optimizer must return correct results on every reuse, not merely for the first convenient value.
Eligibility also differs from cost. An eligible filtered index can lose to another access path when the table is small or the requested output is broad. Keep those questions separate in the review. The index can be safe but unattractive, or attractive but unsafe for the reusable statement. SQL Server is allowed to decline a good-looking shortcut.
When a filtered index is ignored, check whether its filter is guaranteed for every execution of the reusable statement.
Reproduce the Case Where a Filtered Index Is Ignored
Create the dedicated sample table in a disposable database and choose unused names. The inputs are synthetic, with a mix of active and inactive records. Enable actual execution plans in SSMS before running the literal SELECT and the sp_executesql call.
The filtered index includes the output column so coverage does not distract from the parameter-safety question. The demonstration does not guarantee a seek on a tiny table. Read the actual plan produced by your server, and use representative data when investigating a real application.
I compare the application statement's parameter types and session settings as well as its text. A literal query typed in SSMS is not automatically equivalent to the submitted request. Forced parameterization can also change a seemingly literal statement into a parameterized form. Confirm the database setting and inspect the plan's parameter information before concluding that the reproduction matches production.
CREATE TABLE dbo.FilteredParameterDemo
(ID int NOT NULL PRIMARY KEY,IsActive bit NOT NULL,NameText varchar(40));
INSERT dbo.FilteredParameterDemo VALUES(1,1,'Active A'),(2,0,'Inactive'),(3,1,'Active B');
CREATE INDEX IX_FilteredParameter_Active
ON dbo.FilteredParameterDemo(ID) INCLUDE(NameText) WHERE IsActive=1;
SELECT ID,NameText FROM dbo.FilteredParameterDemo WHERE IsActive=1;
EXEC sys.sp_executesql
N'SELECT ID,NameText FROM dbo.FilteredParameterDemo WHERE IsActive=@Active;',
N'@Active bit',@Active=1;Inspect Warnings Without Requiring One
Plan XML can include an UnmatchedIndexes warning when filtered indexes did not match the parameterized statement. Open the captured plan XML and search for that element. The warning is useful evidence when present, but its absence is not proof that parameter safety played no role.
The cache query below finds plans containing that warning using the Showplan namespace. It is bounded for manual review and can return unrelated plans, so read the accompanying text. Cached plans can be evicted or unavailable, and ad hoc batches can contain several statements.
Which parameter values must this reusable plan support? That question usually explains more than staring at the index icon. If the application truly requests active rows only, its SQL should express that invariant as a literal predicate. If it supports both states, the design must remain correct for both rather than forcing an index containing only one state.
WITH XMLNAMESPACES(DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT TOP(20) st.text,qp.query_plan
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
WHERE qp.query_plan.exist('//UnmatchedIndexes')=1;
Choose a Literal Invariant or Recompilation
If the request always means active rows, put IsActive equal to one directly in the statement. Other search values can remain parameters. That makes the filtered subset a stable property of the query rather than a value that changes between executions.
OPTION(RECOMPILE) is another approach. It lets the statement compile for the current value rather than preserving one plan for all values. The next query demonstrates that form with sp_executesql. Compare its actual plan with the reusable version and inspect the current parameter value.
Recompilation costs CPU on each execution. That cost matters for a frequently called short request, even if the resulting plan is efficient. Test representative execution frequency and parameter distributions before adopting it. A query that accepts inactive rows still needs a correct plan for those values. Recompile changes the planning opportunity, not the filtered index's contents.
EXEC sys.sp_executesql
N'SELECT ID,NameText FROM dbo.FilteredParameterDemo
WHERE IsActive=@Active OPTION(RECOMPILE);',
N'@Active bit',@Active=1;Add the Filter Column for the Right Reason
Including the filter column can solve coverage or filtered-predicate matching problems in relevant query shapes. It is particularly useful in documented cases where a filtered column is needed by the result or predicate evaluation. That improvement should not be confused with making an unsafe general parameterized plan safe.
An index containing only IsActive equal to one still cannot answer IsActive equal to zero merely because IsActive is included. Add the column when it addresses a concrete coverage or matching need, then retest the original request. Keep the logical subset argument visible.
The following index alteration includes IsActive alongside the output column. Run it in the test database and compare the plans for the same statements. Do not claim it necessarily fixes the generic parameter case. Review forced parameterization separately, because it can undermine a literal-based strategy by changing how the submitted statement is compiled and cached.
CREATE INDEX IX_FilteredParameter_Active
ON dbo.FilteredParameterDemo(ID) INCLUDE(NameText,IsActive)
WHERE IsActive=1 WITH(DROP_EXISTING=ON);Confirm the Workload Result When a Filtered Index Is Ignored
Capture STATISTICS IO, execution behavior, and compilation evidence for representative requests. Check active and inactive parameter values, result correctness, and the application's actual connection settings. A successful index creation does not establish that the intended query used it.
Record why the chosen approach fits the request. A fixed literal fits an invariant. Recompilation fits a tested execution pattern with an acceptable compile cost. A broader index fits a workload that legitimately needs several states. These are design choices with different maintenance and storage costs.
Clean up the sample table in the disposable database after comparison. For the real change, watch the workload after deployment and revisit the assumption when data or application behavior changes. When a filtered index is ignored, prove eligibility first, coverage second, and cost third. That order keeps the fix tied to correct results.
Related reading on this blog: Filtered Indexes and Where They Help and Parameter Sniffing and OPTION (RECOMPILE).
A filtered index is not a complete copy of the table, it is a subset a plan must prove safe to use.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
