{"id":72010,"date":"2017-08-07T06:25:22","date_gmt":"2017-08-07T06:25:22","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=72010"},"modified":"2021-08-24T13:39:23","modified_gmt":"2021-08-24T13:39:23","slug":"monitoring-unmatchedindexes-warning","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/blogs\/monitoring-unmatchedindexes-warning\/","title":{"rendered":"Monitoring UnmatchedIndexes Warning"},"content":{"rendered":"

If you are using filtered indexes in SQL Server, it worthwhile monitoring the UnmatchedIndexes<\/strong> warning, it can give us very interesting insights.<\/p>\n

Filtered indexes and parameterized queries are terrible enemies. I explained a bit about parameterized queries in my article about how to identify them using query store<\/a>, you can read it here<\/a>.<\/p>\n

When a parameterized query is compiled, SQL Server<\/strong> 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.<\/p>\n

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.<\/p>\n

Here comes the warning: When a filtered index could be used for a query if it wasn’t parameterized, the warning appears in the execution plan. Due to that, this warning advises us that a parameterized query could perform better if it wasn’t parameterized.<\/p>\n

Let’s illustrate with an example using the ‘adventureworks’<\/em> sample database: You can download this database here<\/a>.<\/p>\n

The table production.transactionhistory<\/strong> is very good for our example. There are three different types of transactions, specified by the ‘TransactionType’<\/em> field, making this a good candidate for a filtered index. Let’s create one:<\/p>\n

USE<\/span>\u00a0adventureworks2016<\/span>
\n go<\/span> <\/p>\n

CREATE<\/span>\u00a0NONCLUSTERED<\/span>\u00a0INDEX<\/span>\u00a0indquantityfilter<\/span>
\n \u00a0\u00a0ON<\/span>\u00a0production<\/span>.<\/span>transactionhistory<\/span>(<\/span>quantity<\/span>)<\/span>
\n \u00a0\u00a0include<\/span>\u00a0(<\/span>transactiondate<\/span>,<\/span>\u00a0transactiontype<\/span>)<\/span>\u00a0WHERE<\/span>\u00a0transactiontype<\/span>=<\/span>‘P’<\/span>
\n go<\/span>\u00a0 <\/span><\/div>\n

 <\/p>\n

Now we can test some queries, one by one, checking their query plans:<\/p>\n

SELECT<\/span>\u00a0transactionid<\/span>,<\/span>
\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0transactiondate<\/span>,<\/span>
\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0quantity<\/span>
\n FROM<\/span>\u00a0\u00a0\u00a0production<\/span>.<\/span>transactionhistory<\/span>
\n WHERE<\/span>\u00a0\u00a0quantity<\/span>\u00a0<<\/span>\u00a010<\/span>
\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0AND<\/span>\u00a0transactiontype<\/span>\u00a0=<\/span>\u00a0N’P’<\/span>\u00a0 <\/span><\/div>\n

\"query<\/p>\n

SELECT<\/span>\u00a0transactionid<\/span>,<\/span>
\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0transactiondate<\/span>,<\/span>
\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0quantity<\/span>
\n FROM<\/span>\u00a0\u00a0\u00a0production<\/span>.<\/span>transactionhistory<\/span>
\n WHERE<\/span>\u00a0\u00a0quantity<\/span>\u00a0<<\/span>\u00a010<\/span>
\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0AND<\/span>\u00a0transactiontype<\/span>\u00a0=<\/span>\u00a0N’S’<\/span>\u00a0 <\/span><\/div>\n

\"query<\/p>\n

The first query will use the filtered index, the second won’t, because of the predicate value. None of these queries are parameterized.<\/p>\n

Let’s try with a parameterized query:<\/p>\n

 <\/p>\n

DECLARE<\/span>\u00a0@value<\/span>\u00a0NCHAR<\/span>=<\/span>‘P’<\/span> <\/p>\n

SELECT<\/span>\u00a0transactionid<\/span>,<\/span>
\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0transactiondate<\/span>,<\/span>
\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0quantity<\/span>
\n FROM<\/span>\u00a0\u00a0\u00a0production<\/span>.<\/span>transactionhistory<\/span>
\n WHERE<\/span>\u00a0\u00a0quantity<\/span>\u00a0<<\/span>\u00a010<\/span>
\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0AND<\/span>\u00a0transactiontype<\/span>\u00a0=<\/span>\u00a0@value<\/span>\u00a0 <\/span><\/div>\n

\"query<\/p>\n

\"warning<\/p>\n

This time the filtered index isn’t used, although it could, and we get a warning, exactly the UnmatchedIndexes<\/strong> warning.<\/p>\n

If we include the Recompile<\/strong> hint, SQL Server<\/strong> will be able to consider the parameter value and use the filtered index, like below:<\/p>\n

 <\/p>\n

DECLARE<\/span>\u00a0@valor<\/span>\u00a0NCHAR<\/span>=<\/span>‘P’<\/span> <\/p>\n

SELECT<\/span>\u00a0transactionid<\/span>,<\/span>
\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0transactiondate<\/span>,<\/span>
\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0quantity<\/span>
\n FROM<\/span>\u00a0\u00a0\u00a0production<\/span>.<\/span>transactionhistory<\/span>
\n WHERE<\/span>\u00a0\u00a0quantity<\/span>\u00a0<<\/span>\u00a010<\/span>
\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0AND<\/span>\u00a0transactiontype<\/span>\u00a0=<\/span>\u00a0@valor<\/span>
\n OPTION<\/span>\u00a0(<\/span>recompile<\/span>)<\/span>\u00a0 <\/span><\/div>\n

\"query<\/p>\n

The question is how to identify the queries that could be improved with a hint. Again, I already explained how to find query plan warnings in a previous article (here<\/a>), but this one is different, it needs a different query.<\/p>\n

While other warnings appear in the XML query plan as an element below the\u00a0‘Warnings’<\/em> element, this one appears as an attribute of the ‘Warnings’<\/em> element, so the query I exposed in my previous article can’t catch this one.<\/p>\n

Making some slight changes and we are ready to go and find these warnings:<\/p>\n

 <\/p>\n

WITH<\/span>\u00a0xmlnamespaces<\/span>\u00a0(<\/span>DEFAULT<\/span>
\n ‘http:\/\/schemas.microsoft.com\/sqlserver\/2004\/07\/showplan’<\/span>)<\/span> <\/p>\n

SELECT<\/span>
\n [text]<\/span>,<\/span>
\n query_plan<\/span>.<\/span>value<\/span>(<\/span>‘((\/\/UnmatchedIndexes)[1]\/Parameterization\/Object\/@Schema)[1]’<\/span>,<\/span>\u00a0‘varchar(100)’<\/span>)<\/span>\u00a0AS<\/span>\u00a0[Schema]<\/span>,<\/span>
\n query_plan<\/span>.<\/span>value<\/span>(<\/span>‘((\/\/UnmatchedIndexes)[1]\/Parameterization\/Object\/@Table)[1]’<\/span>,<\/span>\u00a0‘varchar(100)’<\/span>)<\/span>\u00a0\u00a0AS<\/span>\u00a0[Table]<\/span>,<\/span>
\n query_plan<\/span>.<\/span>value<\/span>(<\/span>‘((\/\/UnmatchedIndexes)[1]\/Parameterization\/Object\/@Index)[1]’<\/span>,<\/span>\u00a0‘varchar(100)’<\/span>)<\/span>\u00a0\u00a0AS<\/span>\u00a0[Index]<\/span>
\n FROM<\/span>\u00a0\u00a0\u00a0dbo<\/span>.<\/span>Plancachefromdatabase<\/span>(<\/span>‘AdventureWorks2016’<\/span>)<\/span>
\n WHERE<\/span>\u00a0\u00a0query_plan<\/span>.<\/span>exist<\/span>(<\/span>‘\/\/UnmatchedIndexes’<\/span>)<\/span>\u00a0=<\/span>\u00a01<\/span>\u00a0 <\/span><\/div>\n

\"UnmatchedIndexes<\/p>\n

I’m using the ‘planCacheFromDatabase’ function that I explained in a previous article, the code of this function is the following:<\/p>\n

 <\/p>\n

CREATE<\/span>\u00a0FUNCTION<\/span>\u00a0[dbo]<\/span>.<\/span>[planCachefromDatabase]<\/span>\u00a0(<\/span>
\n —\u00a0Add\u00a0the\u00a0parameters\u00a0for\u00a0the\u00a0function\u00a0here<\/i><\/span>
\n @DatabaseName<\/span>\u00a0VARCHAR<\/i><\/span>(<\/span>50<\/span>)<\/span>\u00a0)<\/span>
\n returns<\/span>\u00a0TABLE<\/span>\u00a0AS<\/span>
\n RETURN<\/span>\u00a0(<\/span>\u00a0WITH<\/span>\u00a0xmlnamespaces<\/span>\u00a0(<\/span>default<\/span>\u00a0‘http:\/\/schemas.microsoft.com\/sqlserver\/2004\/07\/showplan’<\/span>)<\/span>
\n SELECT<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0qp<\/span>.<\/span>query_plan<\/span>,<\/span>\u00a0qt<\/span>.<\/span>text<\/span>,<\/span>
\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0statement_start_offset<\/span>,<\/span>\u00a0statement_end_offset<\/span>,<\/span>
\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0creation_time<\/span>,<\/span>\u00a0last_execution_time<\/span>,<\/span>
\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0execution_count<\/span>,<\/span>\u00a0total_worker_time<\/span>,<\/span>
\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0last_worker_time<\/span>,<\/span>\u00a0min_worker_time<\/span>,<\/span>
\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0max_worker_time<\/span>,<\/span>\u00a0total_physical_reads<\/span>,<\/span>
\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0last_physical_reads<\/span>,<\/span>\u00a0min_physical_reads<\/span>,<\/span>
\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0max_physical_reads<\/span>,<\/span>\u00a0total_logical_writes<\/span>,<\/span>
\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0last_logical_writes<\/span>,<\/span>\u00a0min_logical_writes<\/span>,<\/span>
\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0max_logical_writes<\/span>,<\/span>\u00a0total_logical_reads<\/span>,<\/span>
\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0last_logical_reads<\/span>,<\/span>\u00a0min_logical_reads<\/span>,<\/span>
\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0max_logical_reads<\/span>,<\/span>\u00a0total_elapsed_time<\/span>,<\/span>
\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0last_elapsed_time<\/span>,<\/span>\u00a0min_elapsed_time<\/span>,<\/span>
\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0max_elapsed_time<\/span>,<\/span>\u00a0total_rows<\/span>,<\/span>
\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0last_rows<\/span>,<\/span>\u00a0min_rows<\/span>,<\/span>
\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0max_rows<\/span>
\n FROM<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0sys<\/span>.<\/span>dm_exec_query_stats<\/span>
\n CROSS<\/span>\u00a0apply<\/span>\u00a0sys<\/span>.<\/span>dm_exec_sql_text<\/span>(<\/span>sql_handle<\/span>)<\/span>\u00a0qt<\/span>
\n CROSS<\/span>\u00a0apply<\/span>\u00a0sys<\/span>.<\/span>dm_exec_query_plan<\/span>(<\/span>plan_handle<\/span>)<\/span>\u00a0qp<\/span>
\n WHERE<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0qp<\/span>.<\/span>dbid<\/span>=<\/span>db_id<\/span>(<\/span>@DatabaseName<\/span>)<\/span>\u00a0)<\/span> <\/span><\/div>\n","protected":false},"excerpt":{"rendered":"

If you are using filtered indexes in SQL Server, it worthwhile monitoring the UnmatchedIndexes warning, it can give us very interesting insights. Filtered indexes and parameterized queries are terrible enemies. I explained a bit about parameterized queries in my article about how to identify them using query store, you can read it here. When a……<\/p>\n","protected":false},"author":50808,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2,143527],"tags":[4364],"coauthors":[6810],"class_list":["post-72010","post","type-post","status-publish","format-standard","hentry","category-blogs","category-database-administration-sql-server","tag-monitoring"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/72010","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/50808"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=72010"}],"version-history":[{"count":3,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/72010\/revisions"}],"predecessor-version":[{"id":72236,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/72010\/revisions\/72236"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=72010"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=72010"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=72010"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=72010"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}

Hints such as Recompile<\/strong> can be used even with Entity Framework<\/strong>, I explained this in a recent article, you can read here<\/a>.<\/p>\n