Monitoring UnmatchedIndexes Warning

Comments 0

Share to social media

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 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.

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.

Let’s illustrate with an example using the ‘adventureworks’ sample database: You can download this database here.

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

USE adventureworks2016
go

CREATE NONCLUSTERED INDEX indquantityfilter
  ON production.transactionhistory(quantity)
  include (transactiondate, transactiontype) WHERE transactiontype=‘P’
go 

 

Now we can test some queries, one by one, checking their query plans:

SELECT transactionid,
       transactiondate,
       quantity
FROM   production.transactionhistory
WHERE  quantity < 10
       AND transactiontype = N’P’ 

query with filtered index

SELECT transactionid,
       transactiondate,
       quantity
FROM   production.transactionhistory
WHERE  quantity < 10
       AND transactiontype = N’S’ 

query without filtered index

The first query will use the filtered index, the second won’t, because of the predicate value. None of these queries are parameterized.

Let’s try with a parameterized query:

 

DECLARE @value NCHAR=‘P’

SELECT transactionid,
       transactiondate,
       quantity
FROM   production.transactionhistory
WHERE  quantity < 10
       AND transactiontype = @value 

query with warning

warning in property window

This time the filtered index isn’t used, although it could, and we get a warning, exactly the UnmatchedIndexes warning.

If we include the Recompile hint, SQL Server will be able to consider the parameter value and use the filtered index, like below:

 

DECLARE @valor NCHAR=‘P’

SELECT transactionid,
       transactiondate,
       quantity
FROM   production.transactionhistory
WHERE  quantity < 10
       AND transactiontype = @valor
OPTION (recompile) 

query with recompile

Hints such as Recompile can be used even with Entity Framework, I explained this in a recent article, you can read here.

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), but this one is different, it needs a different query.

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

Making some slight changes and we are ready to go and find these warnings:

 

WITH xmlnamespaces (DEFAULT
‘http://schemas.microsoft.com/sqlserver/2004/07/showplan’)

SELECT
[text],
query_plan.value(‘((//UnmatchedIndexes)[1]/Parameterization/Object/@Schema)[1]’, ‘varchar(100)’) AS [Schema],
query_plan.value(‘((//UnmatchedIndexes)[1]/Parameterization/Object/@Table)[1]’, ‘varchar(100)’)  AS [Table],
query_plan.value(‘((//UnmatchedIndexes)[1]/Parameterization/Object/@Index)[1]’, ‘varchar(100)’)  AS [Index]
FROM   dbo.Plancachefromdatabase(‘AdventureWorks2016’)
WHERE  query_plan.exist(‘//UnmatchedIndexes’) = 1 

UnmatchedIndexes from plan cache

I’m using the ‘planCacheFromDatabase’ function that I explained in a previous article, the code of this function is the following:

 

CREATE FUNCTION [dbo].[planCachefromDatabase] (
— Add the parameters for the function here
@DatabaseName VARCHAR(50) )
returns TABLE AS
RETURN ( WITH xmlnamespaces (default ‘http://schemas.microsoft.com/sqlserver/2004/07/showplan’)
SELECT      qp.query_plan, qt.text,
            statement_start_offset, statement_end_offset,
            creation_time, last_execution_time,
            execution_count, total_worker_time,
            last_worker_time, min_worker_time,
            max_worker_time, total_physical_reads,
            last_physical_reads, min_physical_reads,
            max_physical_reads, total_logical_writes,
            last_logical_writes, min_logical_writes,
            max_logical_writes, total_logical_reads,
            last_logical_reads, min_logical_reads,
            max_logical_reads, total_elapsed_time,
            last_elapsed_time, min_elapsed_time,
            max_elapsed_time, total_rows,
            last_rows, min_rows,
            max_rows
FROM        sys.dm_exec_query_stats
CROSS apply sys.dm_exec_sql_text(sql_handle) qt
CROSS apply sys.dm_exec_query_plan(plan_handle) qp
WHERE       qp.dbid=db_id(@DatabaseName) )

Load comments

About the author

Dennes Torres

See Profile

Dennes Torres is a Data Platform MVP and Software Architect living in Malta who loves SQL Server and software development and has more than 20 years of experience. Dennes can improve Data Platform Architectures and transform data in knowledge. He moved to Malta after more than 10 years leading devSQL PASS Chapter in Rio de Janeiro and now is a member of the leadership team of MMDPUG PASS Chapter in Malta organizing meetings, events, and webcasts about SQL Server. He is an MCT, MCSE in Data Platforms and BI, with more titles in software development. You can get in touch on his blog https://dennestorres.com or at his work https://dtowersoftware.com