THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Rob Farley

- Owner/Principal with LobsterPot Solutions (a MS Gold Partner consulting firm), Microsoft Certified Master, Microsoft MVP (SQL Server), and leader of the SQL User Group in Adelaide, Australia. Rob is a former director of PASS, and provides consulting and training courses around the world in SQL Server and BI topics.

Ultimate query tuning

Hi! - Great that you've found this page, but it's no longer here! You can find the content over at:

Published Tuesday, August 20, 2013 12:21 AM by Rob Farley



Derek Colley said:

Hi Rob, thanks for this, good post. Filtered indexes aren't something I use at all at the moment so a great reminder to look into it!

August 19, 2013 8:38 AM

Andre Ranieri said:


Thanks for the post.  I've played around with filtered indexes in the sandbox but don't have any in production.  I'll keep this use case scenario in mind.


Andre Ranieri

August 19, 2013 7:54 PM

Rob Farley said:

Andre - they're useful even if they don't filter out everything. If you have a common filter such as "IsActive = 1", then a filtered index can be really useful then too. It'll shrink the number of rows that appear in the index, reducing the footprint and decreasing the reads accordingly. Imagine you're sorting rows by date, but only interested in those that are Active. Without the filter, you're keeping all the Inactive rows in the index, even though you're not interested in them.

August 19, 2013 8:16 PM

Andre Ranieri said:

Rob - the lightbulb just turned on.  That's a use case I run into fairly often.

Thanks again for the great blog.


August 19, 2013 10:47 PM

Jack Vamvas said:

I've found filtered indexes  useful when the query uses defined subsets and consistent search criteria.

Filtered statistics is another technique to consider. I have some scenarios on

August 21, 2013 1:34 AM

pmbAustin said:

Based on your other blog entry... is there potential for a filtered index like this to "break" queries, by changing the data they return?  If you do 80% of your queries for "IsActive=1", but the other 20% on all rows, will the filtered index cause any danger of it being used when looking at all rows?  Is there a "best practice" for doing things like creating two indexes, one filtered one not, so the optimizer has the best index to choose in each case?

August 21, 2013 5:19 PM

Rob Farley said:

Jack - yes, that's a useful benefit too.

pmbAustin - Indexes shouldn't change the results of a query. If an index doesn't provide sufficient coverage of the table to satisfy a query, the SQL engine won't pretend it does. A filter like IsActive=1 will only be used for queries that include an IsActive=1 predicate.

August 21, 2013 5:55 PM

John Hennesey said:

I agree with Andre - the light bulb just turned on!  I haven't seen it presented in such a manner that makes such sense.  Thanks!

August 22, 2013 4:45 PM

ALZDBA said:

Great post, Rob.

I just feel the need to emphasize the needed SET options when working with filtered indexes, as these settings a mandatory for all connections hitting that table.

So not only at create time of the index !

( bol/technet weren't clear on this : )

Been bit by it once ( on a production system after dev/QA worked fine but apparently not all apps had been tested ).

"UPDATE failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. "

This blog helped me diagnose and solve the issue:

August 23, 2013 3:03 AM

Rob Farley said:

Indeed! The same applies for ANSI_NULLS, which must be ON. There are actually a bunch of things that require those settings, and apparently one day SQL will refuse connections which don't have those settings configured properly.

August 23, 2013 3:11 AM

David Howell said:

I can see lots of usage scenarios for this, thanks for the easy to follow example.

I didn't get what you mean by this part "For the real scenario, that row gets picked up and removed quite quickly, returning the query to the ‘zero reads’ scenario."

In the real scenario where you are looking for order quantity > 1200, why would a row in the index be removed in the query? I think I am just reading that wrong, could you explain it simply?

August 24, 2013 5:43 AM

Rob Farley said:

Hi David,

This example was inspired by some tuning I did for a client a while back. The filter was looking for rows that met a particular condition, because things in that condition needed attention.  Once they had been seen to, the condition wasn't true any more, so the filtered index returned to being empty.

Another scenario could be to have a "Payment Pending" situation. Most people pay straight away, but occasionally you might have something which enters a special "needs attention" state for a short while.

Does that help?


August 24, 2013 5:59 AM

TheSQLGuru said:

Another great opportunity for filtered indexes is where you have large data skew.  You can get the right query plan both ways (either scans/hashes for the skew(ed) values or seeks/nested loops for onesy-twosy values) with indexes that are a fraction of the normal index size.

August 24, 2013 1:37 PM

Rob Farley said:

Yes - I see a lot of potential around filtered indexes. They also help if you have a fixed inequality predicate and want the data ordered by something else. So much potential...

August 25, 2013 10:07 AM

Ian Stirk said:

Great post, a very useful and smart algorithm...


August 30, 2013 6:51 AM
Anonymous comments are disabled

This Blog



No tags have been created or used yet.


News? Haven't you read my blog?

My Company

Can't find something?

Contact Me

Twitter: @rob_farley
Skype: rob_farley

MVP (SQL Server)


Adelaide SQL UG


Privacy Statement