A unique filtered index does not guarantee that every cardinality-estimator version will infer one row for every parameterized query. Capture an actual plan, compare estimated and actual rows, and verify that the query predicate logically matches the index filter.
Last updated: October 31, 2026.
SET STATISTICS XML ON;
SELECT ItemId, ExternalId
FROM dbo.Items
WHERE IsActive = 1
AND ExternalId = @ExternalId
OPTION (RECOMPILE);
SET STATISTICS XML OFF;Use the recompiled statement as a diagnostic comparison, not an automatic permanent fix. In the plan, inspect the access operator, estimated rows, actual rows, and statistics used. Save both the normal and recompiled plans so you can compare assumptions rather than only elapsed time across several representative parameter values.
Verify the filter and statistics
Microsoft recommends comparing estimated and actual plans when investigating cardinality-estimation problems. Confirm that the filtered index predicate uses the same data types and logic as the query. Implicit conversions, a parameter that hides a constant, or a predicate that does not imply the filter can keep the optimizer from using the expected estimate.
Review the filtered statistics date and sampled rows. Update stale statistics with an appropriate sample, then repeat the same test with representative parameter values. Record logical reads and duration as well as row estimates because a numerically imperfect estimate may still produce the best practical plan.
Test estimator changes at narrow scope
Different database compatibility levels can select different cardinality-estimator behavior. Do not change the entire database because one statement improves under another estimator. Compare plans in a test environment, use Query Store to measure the statement over time, and consider a documented query-level hint or Query Store hint only after confirming the tradeoff.
Microsoft’s filtered-index guidance also stresses choosing a well-defined subset and matching the query predicate. Continue with finding Query Store plans by index, diagnosing optimizer join choices, and understanding SQL Server parameters.
