Filtered indexes and parameters

2013-12-01

Filtered indexes and parameters

Filed under: Uncategorized — ---- @ 8:32 PM

SQL Server 2008 introduced filtered indexes.

While there are scenarios were filtered indexes are a great solution there is a simple scenario where SQL Server can’t use these type of indexes (for a good reason): predicates with variables/parameters.

Example (I used Adventure Works sample database for SQL Server 2012). If we create the following filtered index for some queries that shows the sales orders having SubTotal >= 100000:

Code Snippet
USE AdventureWorks2012;
 GO
 CREATE INDEX INF_SalesOrderHeader_SubTotal
 ON Sales.SalesOrderHeader(SubTotal)
 INCLUDE (OrderDate)
 – Only 54 orders from 31564 orders have SubTotal greather than 100000
 WHERE SubTotal >= 100000; 
 GO

then the execution plans of the following query

Code Snippet
– Query #1
 SELECT    OrderDate, SubTotal
 FROM    Sales.SalesOrderHeader h
 WHERE    h.SubTotal >= 105000 
 GO

will include an Index Seek on this index:

But if we rewrite this query to use a variable / parameter instead of constant:

Code Snippet
– Query #2
 DECLARE @p MONEY = 105000 
 SELECT    OrderDate, SalesOrderID
 FROM    Sales.SalesOrderHeader h
 WHERE    h.SubTotal >= @p
 GO

then the execution plan will include an index scan on clustered index PK_SalesOrderHeader_SalesOrderID because the execution plan must be generated for all values of @p (less than 100000 or greater than 100000):

What solutions do we have if we want to show only those orders with SubTotal >= 100000 but the the minimum subtotal must be a variable / parameter ?

The usual solution for this problem is to simple add the RECOMPILE query hint thus:

Code Snippet
– Query #3
 DECLARE @p MONEY = 105000 
 SELECT    OrderDate, SalesOrderID
 FROM    Sales.SalesOrderHeader h
 WHERE    h.SubTotal >= @p
 OPTION (RECOMPILE)
 GO

But what if we want to benefit from execution plan caching ? The solution is to add the predicate from WHERE clause of CREATE INDEX statement within WHERE clause of SELECT statement thus WHERE CreateIndexWherePredicate AND PredicateWithVariable:

Code Snippet
– Query #4
 DECLARE @p MONEY = 105000 
 SELECT    OrderDate, SalesOrderID
 FROM    Sales.SalesOrderHeader h
 WHERE    h.SubTotal >= 100000  AND h.SubTotal >= @p
 GO

This way SQL Server knows that it must shows always those orders having SubTotal >= 100000 or greater and thus it can use safely the filtered index.

Note: here can be found all predicates (IN, IS | IS NOT | = | <> | != | > | >= | !> | < | <= | !<) allowed within WHERE clause of CREATE INDEX statement.

Note #2: The execution plan for Query #2 shows an UnmatchedIndexes warning that can be found by examining the XML execution plan:

Code Snippet
            <UnmatchedIndexes>
               <Parameterization>
                 <Object Database="[AdventureWorks2012]" Schema="[Sales]" Table="[SalesOrderHeader]" Index="[INF2_SalesOrderHeader_SubTotal]" />
               </Parameterization>
             </UnmatchedIndexes>
             <Warnings UnmatchedIndexes="true" />

Note #3: You can detect these UnmatchedIndexes warnings also by using an Extended Event session and sqlserver.unmatched_filtered_indexes event:

Code Snippet
CREATE EVENT SESSION [XEWarnings] ON SERVER 
 ADD EVENT sqlserver.unmatched_filtered_indexes(
     ACTION(sqlserver.database_id,sqlserver.database_name,sqlserver.sql_text)
     WHERE ([sqlserver].[database_name]=N'AdventureWorks2012')) 
 ADD TARGET package0.ring_buffer
 GO

About these ads

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Yet another SQL Server enthusiast

2013-12-01