If you are using filtered indexes, it worth monitor the UnmatchedIndexes warning, it can give us very interesting insights.

Filtered indexes and parameterized 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 not taking into account the parameter value, the query plan needs to be optimized for any possible value.

Filtered indexes, on the other hand, doesn’t contain all the possible values of a key, they are filtered. The obvious result: 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, turning this a good scenario for a filtered index. Let’s create one:

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’ 

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

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:

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:

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 ‘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:

I’m using the ‘planCacheFromDatabase’ function 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) )

Let’s block ads! (Why?)

SQL – Simple Talk

Search for: