UnmatchedIndexes appears even when the index is used
The UnmatchedIndexes warning, that should appear only when the query is parameterized and SQL Server is unable to use an existing filtered index, is appearing even when the query isn't parameterized and the filtered index is used.
AdminMicrosoft SQL Server
(Product Manager, Microsoft Azure)
shared this idea
Upvotes: 2
<=-=Aug 7 2017 1:17PM=-=>Quick question, optimized for ad hoc workloads?
You might have found this article by Rob Farley, did a good job in describing
the limitations and references others on the limitations of filtered indexes.
http://sqlblog.com/blogs/rob_farley/archive/2015/02/22/why-isn-t-my-filtered-index-being-used.aspx
I haven’t had much luck with them due to the highly parameterized nature
of the systems I am responsible for. Real hit and miss.
Wish you luck.
2 comments
-
Taiob
commented
Issues still exist in SQL Server 2019 CU1. Same symptom as described by Kendra. Details of my findings here (http://sqlworldwide.com/unmatchedindexes-bug-in-ssms/).
-
Kendra Little
commented
I am seeing this problem occur as well against SQL Server 2017. The index is used in my plan, that's fine, but I *also* get an unmatched indexes warning, which lists the index that is actually being used.
The index IS matched, but the actual plan shows a warning that it was not.