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:
1 2 3 4 5 |
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:
1 2 3 |
SELECT SalesOrderID FROM Sales.SalesOrderHeader AS soh WHERE OnlineOrderFlag = 1 ; |
There’s No Problem Here!
Let’s create a pair of stored procedures:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
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.
21 Comments. Leave new
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.
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.
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.
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.
That sucks big time! I thought that only indexed views were such a beast.
Unfortunately the error message clearly lists all the problematic objects: “indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index”
I attended your Dynamic SQL session at SQL PASS and it was great! I am trying to find your demo examples.
Susan Pace
You can download the resources at http://www.sqlpass.org/EventDownload.aspx?suid=2798
Great Post.
Thanks!
Hi brentozar team,
I really found this article very useful. Thanks.
Luckily, I am using literals inside SPs for Filtered indexes. 🙂
Karthik.
I have gigantic tables. If my understanding about statistics is correct this could lead to performance reduction. If i create filtering index should i create same filtering statistics base on same predicate ? Do query optimizer reconizer this filtering statistics ? Thank you and please pardon me for my rude taste of word.
If you create a filtered index, it will have a filtered statistics. There’s no need to create another. The optimizer will consider the filtered index and use the underlying filtered statistic during query optimization.
Unfortunately using this type of pattern to concatenate variable data into the query string and create the query now opens us up to SQL injection. However unlikely in the scenario provided I could see the basis of this idea used and introduce such an attack surface. So take care in how and where you might use this.
I’m curious how you plan to convert a
BIT
parameter into a a vector for a SQL injection attack. Can you share how you’d imagine that to work out?Sorry, my comment misled you from my intended meaning. When I said “However unlikely in the scenario provided”, I meant that for your given example it was unlikely to be at risk.
But if people apply the “pattern” or idea of concatenating variable data to create the query string without concern for the variable types and what data they may contain then it opens you up for sql injection. That is why I mentioned “So take care in how and where you might use this.”
Hopefully this clears up what i was trying to convey.
It makes me nervous too … my immediate reaction whilst reading the article was that I would prefer to code
SET @sql += ‘WHERE OnlineOrderFlag = ‘ + CAST(@OnlineFlag AS NVARCHAR(1));’
as
IF @OnlineFlag = 1
SET @sql += ‘WHERE OnlineOrderFlag = 1;’
ELSE
SET @sql += ‘WHERE OnlineOrderFlag = 0;’
(or some variation of that 🙂 )
We do use deliberate, hard-coded, variations like this (even if they are just in comments) to differentiate query variations so they each get their own plan. But I’m no expert …
this article just saved me half a day of fruitless trial and error. Thanks!
Good explanation. One small bug – there’s:
EXEC jp_online_only_params ‘2007-12-12’, 0;
and above never uses designed index (WHERE OnlineOrderFlag = 1).
You can even go to the next step with dynamically managing a filtered index 😉
For a client this was a real light saver!
http://clementhuge.over-blog.com/2017/02/the-dynamic-filtered-indexes.html