Learn T-SQL Querying

UnmatchedIndexes*

UnmatchedIndexes* happens when the Query Optimizer cannot match an existing filtered index with a query predicate due to parameterization.

SQL Server can use optimized non-clustered indexes that are defined using a WHERE clause. These are called filtered indexes and are especially suitable for narrow query coverage. Being defined on a subset of data, these indexes can significantly improve query performance.

The following example creates a filtered index in the AdventureWorks sample database, and then executes a query with an unmatched index warning:

CREATE NONCLUSTERED INDEX FIProductAccessories ON Production.Product (    ProductSubcategoryID,     ListPrice) INCLUDE (Name)WHERE ProductSubcategoryID >= 27 AND ProductSubcategoryID ...