Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase ««12

Stairway to SQL Server Indexes: Step 7, Filtered Indexes Expand / Collapse
Posted Wednesday, August 24, 2011 12:52 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, January 22, 2016 4:05 AM
Points: 6, Visits: 76
I agree that the 'lexical equivalence' statements are incorrect. This is a common misconception about filtered indexes. They would be pretty useless if where clauses had to be identical between filtered indexes and query filters.
In order to get the filtered index used in wider filtering scenarios, where there is not a direct match with query filter and index filter, you will need to make sure that the column in the filter is either in the index key (making wider b-trees) or in the include (making fatter leaf level data, not so bad as in the key perhaps).
The other gotcha mentioned regarding use of parameters and precompiled query plans (see post by SSC Veteran) is a real pain. You can do the rather painful (and not always feasible due to performance considerations) 'option (recompile)' on the query statement in question, or even force a stored proc recompile, with various messy implications. Using index hints in this scenario is only any use if you are sure that the parameter value will make the filtered index you are forcing a good idea (fine if all your parameter values fall into the filter condition but otherwise not really helping).
I think the autoparameterisation issue above is something really lacking here. There should in my mind be some aspect of 'parameter sniffing' that takes this into account. I feel a 'Connect' submission is calling...
The BOL entry for this subject is very misleading and does not really explain the point regarding what is required internally in the index for filters to really work. Is it my impression or has BOL gone downhill since 2008? The quality of some of the latest tech articles are really not cutting it.
Post #1164924
Posted Thursday, August 25, 2011 8:27 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, November 29, 2016 5:04 PM
Points: 556, Visits: 971
All the critical comments aside (regarding proofreading), I liked the article. I haven't used filtered indexes previously and this gave me a good idea when I might want to try them. Thank you!

Post #1165394
Posted Monday, August 29, 2011 8:31 AM



Group: General Forum Members
Last Login: Thursday, August 11, 2016 12:53 PM
Points: 179, Visits: 733
Also, anyone implementing filtered indexes might want to check their stored procedures to see if they were written under "set quoted_identifier off". I created filtered indexes and suddenly several stored procedures were streaming dumps.

Here's a starting place at learning more about that...
Post #1166933
Posted Monday, April 2, 2012 11:56 AM

Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 6:08 AM
Points: 549, Visits: 1,883
We have a screen which displays all records relevant to a particulart screen.
We also only show records where the status = 1.
The DB has never been archived so I tried an index on cola, colb,include col c,d,e, where status = 1.
Each query on (Cola and colb) returns about 150 records.

The index total size is 3 Megs (400Megs Data) since there are only 80000 active records from a table of 6.5 million rows.

This does not work for all the queries but makes another index 80% redundant.
The io generated from using this index was much lower than the io generated from the full table index.

I did find that there are still a couple of queries not using this index due to poor query design. (isnull and coalesce on columns)

All in all, this article was a great place to start.

Catch-all queries done right
Gail Shaw's Performance Blog
Post #1276815
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse