February 14, 2011 at 10:37 am
Comments posted to this topic are about the item Stairway to SQL Server Indexes: Step 7, Filtered Indexes
February 17, 2011 at 10:05 am
So a filtered index reduces the number of columns and (importantly) the number of rows you need to search, whereas an indexed view... does pretty much the same thing. Where would you use one over the other? We have not moved to SQL 2008 yet but we've had great success under certain conditions, putting an indexed view over a given table. The sorts of things we look out for are the need to regularly query a small stable subset of the table using an uncomplicated where clause.
...One of the symptoms of an approaching nervous breakdown is the belief that ones work is terribly important.... Bertrand Russell
February 22, 2011 at 5:09 am
In your article you write:
"You might be surprised to learn that changing the WHERE clause in the previous statement from "SpecialOfferID <> 1" to "SpecialOfferID = 2" will prevent SQL Server from using the filtered index. This is because SQL Server compares the WHERE clause of the SELECT statement against the WHERE clause of the CREATE INDEX statement for lexical equivalence, not for logical equivalence. Therefore, SQL Server does not realize that the filtered index covers the query."
While this is true, it only affects queries with predicates containing the same column(s) used in the filtered index predicate. Moreover, it should be pointed out that including the columns used in the filtered index predicate (SpecialOfferID in this case) either in the index key or included columns, will allow the optimizer to consider the filtered index (provided it is valid for the query predicate of course).
ie:
CREATE NONCLUSTERED INDEX FK_ProductID_ModifiedDate
ON Sales.SalesOrderDetail (ProductID,ModifiedDate)
INCLUDE (OrderQty,UnitPrice,LineTotal,SpecialOfferID) -- NOTE the addition of the predicate-column)
WHERE SpecialOfferID <>1
Is valid and will be used for the following query:
SELECT ProductID ,
ModifiedDate ,
SUM(OrderQty) 'No of Items' ,
AVG(UnitPrice) 'Avg Price' ,
SUM(LineTotal) 'Total Value'
FROM Sales.SalesOrderDetail
WHERE SpecialOfferID = 2
GROUP BY ProductID ,
ModifiedDate
Of course the optimizer may choose not to use the filtered index if the query predicate is not selective enough in which case you may decide to force the optimizer's hand with an index hint:
...WITH (INDEX(FK_ProductID_ModifiedDate)) WHERE SpecialOfferID = 2 ...
While index hints can force a plan to utilize a filtered index (forcing even our filtered index without an 'INCLUDED SpecialOrderID' to be used) they should not substitute well-designed indexes. If the optimizer isn't selecting your filtered index, check if it's covering the query. Would a non-filtered index be valid and cover the same query? If not, try adding the column(s) used in the query predicate to the filtered index's included columns (or key if appropriate). If this doesn't help, check your cardinality estimates. Are your stats up to date? etc...
David's done a stirling job on this Stairway so it's disappointing that he's not kept to the same high standard on this level. Many more explanations and considerations on filtered indexes can be found at: http://msdn.microsoft.com/en-us/library/cc280372(v=sql.105).aspx
June 29, 2011 at 1:46 am
Something wrong with the listings....
August 23, 2011 at 10:49 pm
dronusoid (6/29/2011)
Something wrong with the listings....
Quite a few typos too - happy to proof-read articles if it would help!
August 24, 2011 at 2:59 am
I was disappointed to find that the first example of a filtered index was over half way down the article, that put the whole top half not quite out of context, but sort of out of focus. Also an explanation of the lexical v logical limitation might have been useful before getting us interested in a feature that is basically crippled beyond virtually all usefulness by that one limitation (i.e. only if you're going to use the exact query in the filtered index will it actually work, IRL that probably eliminates 90%+ of potential uses). That limitation does put the whole article out of context, because every reader for whom a logical comparison would have made it useful (Over 10x as many as for whom it is actually useful) is mentally extrapolating impossible uses for it and then you slam them down to earth with an annoying thump at the end.
August 24, 2011 at 4:05 am
I like filtered indexes but have ran into a very annoying issue with them once.
The where clause of a filtered index cannot have OR operators in it (it does support AND, IN and NOT IN). But as soon as you want to filter using more complex conditions you hit a brick wall! Computed columns can also not be used, thus there is no way around this limitation.
A good use for unique filtered indexes is to apply a business rule on a nullable field by using a where IS NOT NULL. In many cases there is no alternative way of doing this as a regular unique index will allow for only one occurence of a NULL value. This form of filtering improves performance in multiple ways, one is uniqueness versus a regular non-unique index and the other is by accessing less indexed rows and thus operating on a smaller index.
One final comment:
Use of filtered indexes only works with non-parameterised where conditions
A where type = 1 will work, but a parameterised where type = ? with 1 as the parameter value will not. This is because the queryplan will be generated to handle any input value for the parameter. This is implicitly incompatible with a filtered index that singles out a single (set) of values.
August 24, 2011 at 6:41 am
My database design requires the use of bit fields, the majority of which will be false, and it would be nice to have a filtered index for bit fields that are true.
Does this work? Or will the optimizer skip over bit field indexes?
August 24, 2011 at 7:52 am
roger.plowman (8/24/2011)
My database design requires the use of bit fields, the majority of which will be false, and it would be nice to have a filtered index for bit fields that are true.Does this work? Or will the optimizer skip over bit field indexes?
Give it a try. Run this and look at the execution plan.
if OBJECT_ID('dbo.test') is not NULL
drop table dbo.test
create table dbo.test (i int primary key,
isinactive bit
)
create index FIX_Test_Active on dbo.test (i)
where isinactive = 1
go
with i as (select top 8000 ROW_NUMBER() over (order by a.object_id) id
from sys.columns a, sys.columns b)
insert into test
select id,
case
when (id % 2) = 0 then 1
else 0
end
from i
select i from dbo.test where isinactive = 1
You can also turn stats IO and CPU time on and see how much of a time savings there was.
August 24, 2011 at 9:19 am
This article needs to be proof read by somebody, lots of mistakes in listings and missing queries to try.
Should never have brought in the 3rd normal form stuff.
Thomas
Thomas LeBlanc, MVP Data Platform Consultant
August 24, 2011 at 12:52 pm
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.
August 25, 2011 at 8:27 am
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!
August 29, 2011 at 8:31 am
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... http://blogs.msdn.com/b/sqlprogrammability/archive/2009/06/29/interesting-issue-with-filtered-indexes.aspx
April 2, 2012 at 11:56 am
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.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply