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:
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:
transactiondate,
quantity
FROM production.transactionhistory
WHERE quantity < 10
AND transactiontype = N’P’
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:
SELECT transactionid,
transactiondate,
quantity
FROM production.transactionhistory
WHERE quantity < 10
AND transactiontype = @value
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:
SELECT transactionid,
transactiondate,
quantity
FROM production.transactionhistory
WHERE quantity < 10
AND transactiontype = @valor
OPTION (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:
‘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
I’m using the ‘planCacheFromDatabase’ function that I explained in a previous article, the code of this function is the following:
— 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