Filtered Index not being used - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Filtered Index not being used

Author

Topic

dhw
Constraint Violating Yak Guru

332 Posts

Posted - 2010-03-25 : 16:53:35

Hi All

I have created a filtered index on a table and when I run a query that should use that index, the Execution Plan shows that the query used the Tables Clustered Index. And, it also indicates that there is a Missing Index and suggests that I create a nonclustered index on the column I have already filtered on.

My table is like:


  CustomerID  int  Identity  PK
  CustomerName  varchar(50)
  CustomerSegment varchar(50)
  CustomerCreateDate datetime
  (and about 10 other columns)

Because there are many queries that are based on the CustomerSegment, I have created 4 filtered indexes based on this column. As background, there are only 4 different customer segments.
The filtered indexes are all like:


CREATE NONCLUSTERED INDEX CustomerSegment_Gold
ON Customer (CustomerID)
WHERE CustomerSegment = 'Gold'

I run a query like


Select CustomerID, CustomerName 
From Customer 
Where CustomerSegment = 'Gold'

..and I expected the Exec Plan to show the new filtered index. Instead, it only shows the Clustered Index (on the Customer ID pk).

Is this normal? Or, am i doing something wrong?

Thanks
- will

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-03-25 : 16:59:06

I don't know if this is normal or if you are doing anything wrong as I haven't used SQL2k8 much yet, but perhaps I can help on this anyway.

I'd suggest comparing the execution plan of the query like you have it and then also with an index hint with your filtered index. Run them in the same batch so that you can see the cost of each. If the filtered index shows marked improvement over the other one, then I'd suggest clearing the procedure cache or recompiling the stored procedure (if this is in a proc, that is). Then check the execution plan again. If the new plan is using the filtered index, then you just had a bad plan in cache. If the filtered index still isn't being used though and you are convinced that the query optimizer should use it, then add a permanent index hint to the query.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

dhw
Constraint Violating Yak Guru

332 Posts

Posted - 2010-03-25 : 17:00:06

Okay, I figured it out.

I forgot to include the column, in this case the CustomerSegment in the column list.

So, creating the index like


CREATE NONCLUSTERED INDEX CustomerSegment_Gold
ON Customer (CustomerID, CustomerSegment)
WHERE CustomerSegment = 'Gold'

This causes my query to use the index.

- will

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-03-25 : 17:04:05

Cool, glad you figured it out!

You may want to add CustomerName as an INCLUDE column so that it doesn't need to do a lookup on the clustered index.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

dhw
Constraint Violating Yak Guru

332 Posts

Posted - 2010-03-26 : 01:26:23

Oh yeah, I forgot about that. There are definitely some other columns I should include as well. Thanks for the suggestion.

Kristen
Test

22859 Posts

Posted - 2010-03-26 : 04:39:04

It would save some index space if you didn't need to include the column itself where it could only be one value ...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources