Blog

I’ve been told that an attendee at the PASS Summit pre-conference event asked about using dynamic SQL to get around some of the problems with filtered indexes. I’m not entirely sure what the question was, but it did give me the opportunity to play around with filtered indexes and write some simple demo code to illustrate just the kind of shenanigans that you can get up to.

Creating a Filtered Index

The first step for our demo is to create a filtered index. Grab yourself a copy of Adventure Works and get ready for some fun. Here’s our filtered index:

CREATE INDEX jp_filters_not_offline 
    ON Sales.SalesOrderHeader (OrderDate) 
        INCLUDE (SalesOrderID)
        WHERE OnlineOrderFlag = 1 ;
GO

That creates our initial filtered index. We want to search by OrderDate and find only those orders that were placed online. This is a realistic index to create in the AdventureWorks database, too – there are 27,659 online sales in my copy of AdventureWorks, but only 3,806 offline sales.

Want to prove that the index works? Run this SQL and take a look at the execution plan:

SELECT  SalesOrderID
FROM    Sales.SalesOrderHeader AS soh
WHERE   OnlineOrderFlag = 1 ;

There’s No Problem Here!

Let’s create a pair of stored procedures:

CREATE PROCEDURE jp_online_only_date
(
    @OrderDate DATETIME
)
AS
BEGIN
    SELECT SalesOrderID
    FROM Sales.SalesOrderHeader AS soh
    WHERE OnlineOrderFlag = 1 AND OrderDate > @OrderDate
END
GO

CREATE PROCEDURE jp_online_only_date_2
(
    @OrderDate DATETIME
)
AS
BEGIN
    DECLARE @sql NVARCHAR(MAX) = N'';

    SET @sql += 'SELECT SalesOrderID
FROM Sales.SalesOrderHeader AS soh
WHERE OnlineOrderFlag = 1 AND OrderDate > '''
+ CAST(@OrderDate AS NVARCHAR(MAX)) + ''';'

    EXEC sp_executesql @sql;
END

If you were to run both of these, you’d notice that they both make use of the filtered index. What gives?

Both of these stored procedures are able to take advantage of the filtered index because the filter condition (OnlineOrderFlag = 1) is matched by predicate in the query. In other words – SQL Server can easily make this match.

The Problem with Parameters

There’s a problem, though. When we switch over to using a parameter, SQL Server isn’t able to make effective use of the filtered index. Check it out:

CREATE PROCEDURE jp_online_only_params
(
    @OrderDate DATETIME,
    @OnlineFlag BIT
)
AS
BEGIN
    SELECT  SalesOrderID
    FROM    Sales.SalesOrderHeader AS soh
    WHERE   OnlineOrderFlag = @OnlineFlag
            AND OrderDate > @OrderDate ;
END 
GO

EXEC jp_online_only_params '2007-12-12', 0 ;

Oh no! This procedure doesn’t use our filtered index any more. There’s got to be a way to trick SQL Server into using our filtered index. We could resort to an index hint, but that would mean we have to keep using the same index or keep re-using the name of that index. I’m not a big fan of that, so let’s think of something else.

What if we were to use string concatenation? That sounds like magic, it has a lot of syllables.

Hooray for Strings!

We can solve the problem by forcing SQL Server to see the literal value that we’re passing in:

CREATE PROCEDURE jp_online_only_strings
(
    @OrderDate DATETIME,
    @OnlineFlag BIT
)
AS
BEGIN
    DECLARE @sql AS NVARCHAR(MAX) = N'';

    SET @sql += 'SELECT SalesOrderID
FROM Sales.SalesOrderHeader AS soh
WHERE OnlineOrderFlag = ' + CAST(@OnlineFlag AS NVARCHAR(1));

    SET @sql += ' AND OrderDate > '''
        + CAST(@OrderDate AS NVARCHAR(MAX)) + ''';'

    PRINT @sql

    EXEC sp_executesql @sql;
END

If you were to run that stored procedure, you’d get effective use of the filtered index. SQL Server sees the OnlineOrderFlag predicate as a literal value, matches that up to the index definition, and we’re off to the races.

There is one problem, though – because we’re also using a literal value for OrderDate, there could be significant bloat in the plan cache. SQL Server will create one execution plan for every possible combination of parameters that we pass in. In AdventureWorks there are 1,124 distinct values in the OrderDate column. That’s a lot of execution plans.

Avoiding Plan Cache Bloat

We want to avoid bloat in the execution plan cache. In order to do that, we’re going to need to parameterize part of the query but still leave the filter intact. That’s really easy to do:

CREATE PROCEDURE jp_online_mixed
(
    @OrderDate DATETIME,
    @OnlineFlag BIT
)
AS
BEGIN
    DECLARE @sql AS NVARCHAR(MAX) = N'';

    SET @sql += 'SELECT SalesOrderID
FROM Sales.SalesOrderHeader AS soh
WHERE OnlineOrderFlag = ' + CAST(@OnlineFlag AS NVARCHAR(1));

    SET @sql += ' AND OrderDate > @order_date;'

    PRINT @sql

    EXEC sp_executesql @sql,
         N'@order_date DATETIME',
         @OrderDate;
END

By adding the @OnlineFlag as just another string, but still parameterizing the @order_date we end up with only two execution plans. One plan will be created for each value of@OnlineFlag, but we’ll still get use of the filtered index. This is all but impossible to accomplish with forced parameterization and difficult to accomplish with regular parameteriziation (usually you have to resort to index hints).

Summing It Up

Dynamic SQL and filtered indexes are a great match. By carefully making use of dynamic SQL, you can coerce SQL Server into behaving well and providing you with an execution plan that uses the filtered index. Through fully parameterized dynamic SQL you can get good plan re-use and avoid SQL injection attacks. By making careful use of dynamic SQL and filtered indexes, you should be able to get performance out of SQL Server without having to resort to tricks like index hints or plan guides.

↑ Back to top
  1. Why the proc uses filtered index in case of Dynamic sql.

    • By concatenating in the literal value of @OnlinrFlag, the optimizer sees the constant and is able to match it with the filtered index. Parameters and variables are like a cardboard box for values – they’re handy for moving a value around, but they make it impossible to see the actual value.

  2. I guess the point is that one of the benefits of dynamic SQL is to override the parameter sniffing problem, as well as by using With Recompile hint.

    • That’s exactly it. I like using dynamic SQL before I resort to a recompile hint. If I end up with a few variations of the plan, that’s better to me than having a large number of recompiles going on.

      It doesn’t suit all use cases, though.

  3. Hey, awesome article. Another “issue” I found with filtered indexes is that they may not work with your data access technology. For instance, if your ORM uses non-default ANSI settings then you will actually see that not only is the index not used, but INSERT statements start throwing errors everywhere. I did a write up on this (http://www.davewentzel.com/content/gotcha-sql-server-filtered-indexes) with a repro script.

  4. Excuse my literal English (Google translator gives what he can: P) …. My native language is Spanish, but I probably understand better the mash ….

    Before performing the SP direct variables without using the dynamic or sp_executesql within the SP’s.

    Also, I have used the techniques for the girl or Mrs. published as to recompile and go further in the high security provided by me and that before risking me for some SQL injection, I tested the plan optimized, and the use of cache, use filtered indexes, and boy did that cost reduction, run the CPU and agility in bringing the data has improved me.

    Publication and clearly appreciate the attention to detail you better explain what at first glance appears to be something useful or understood in real life.

    Best regards.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

css.php