UnmatchedIndexes – Bug in SSMS

January 21, 2020

Filtered Index in SQL Server and Parameterized Query does not work well with each other. This incompatibility is a well-known issue among Data Professionals. If you do a Google search for ‘SQL Server unmatched index‘ you find many articles written by well-known bloggers in the SQL Server community.

I am quoting a summary of the issue from Dennes Torres’s (Blog|Twitter) article Monitoring UnmatchedIndexes Warning.

When a parameterized query is compiled, SQL Server is unable to take into account the value of the parameter. Of course, it’s a bit more complicated than that, but I will not dig into all the details in this blog. The main problem is that, because it cannot take into account the parameter value, the query plan has to be optimized for every value.

Filtered indexes, on the other hand, doesn’t contain all the possible values of a key, because they are filtered. The obvious result of this is that parameterized queries can’t use filtered indexes because they don’t fit any possible value.

https://www.red-gate.com/simple-talk/blogs/monitoring-unmatchedindexes-warning/

SQL Server Management Studio (SSMS) showplan root node properties have two attributes to indicate when a filtered index can not be used during a parameterized query. One is ‘UnmatchedIndexes’, and the other one is under ‘Warnings’ attribute. Due to a bug in SSMS, the attributes are also showing positive results with filtered index and non-parameterized queries.

Let’s look at an example. I am using the AdventureWorks database, which you download from here.

Creating a filtered index on ‘Sales.SpecialOfferProduct’ tables ‘SpecialOfferID’ column.

USE [AdventureWorks]
GO
--drop index if exists
DROP INDEX IF EXISTS idx_filter_SpecialOfferID ON Sales.SpecialOfferProduct
GO
--create index
CREATE NONCLUSTERED INDEX idx_filter_SpecialOfferID
  ON Sales.SpecialOfferProduct(SpecialOfferID)
  WHERE SpecialOfferID=1;
GO

Trying to use that index with a parameterized query. Turn on the Actual Execution Plan (Ctrl+M).

USE [AdventureWorks]
GO
DECLARE @offerId SMALLINT =1;
SELECT COUNT(DISTINCT productID)
FROM Sales.SpecialOfferProduct
WHERE SpecialOfferID=@offerId;

We can see the warning as expected in the actual execution plan regarding the Unmatched Index. Look at the properties of the root node.

Now running the same query without the parameter and hardcoding the value of SpecialOfferId. We should not see the warning about ‘UnmatchedIndexes’.

USE [AdventureWorks];
GO
SELECT COUNT(DISTINCT productID)
FROM Sales.SpecialOfferProduct
WHERE SpecialOfferID=1;

I still see the same warning. It is not a parameterized query and on top of it, the index was used.

This bug was first reported on August 03, 2017, as a feedback item. Kendra Little (Twitter) posted a comment on May 18, 2018, confirming the bug exists in SQL Server 2017. I tested this on SQL Server 2019 CU1 and still exists.

Please UpVote the feedback item, so it gets enough attention from the product managers.

1 reply on “UnmatchedIndexes – Bug in SSMS”

Leave a Reply to Bug with UnmatchedIndexes and Filtered Indexes – Curated SQL Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.