Simple Talk is now part of the Redgate Community hub - find out why

Monitoring UnmatchedIndexes Warning

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

How you log in to Simple Talk has changed

We now use Redgate ID (RGID). If you already have an RGID, we’ll try to match it to your account. If not, we’ll create one for you and connect it.

This won’t sign you up to anything or add you to any mailing lists. You can see our full privacy policy here.

Continue

Simple Talk now uses Redgate ID

If you already have a Redgate ID (RGID), sign in using your existing RGID credentials. If not, you can create one on the next screen.

This won’t sign you up to anything or add you to any mailing lists. You can see our full privacy policy here.

Continue