Filtered indexes and statistics

Question

Filtered indexes and statistics

Gail Shaw

SSC Guru

Points: 1004504
More actions
June 18, 2009 at 4:52 am

#211080

Hi all
I'm interested to know how many people who are using 2008 have used either filtered indexes, filtered statistics or both. If you have, can you please let me know what your experience with them is.
Disclaimer: I'm doing presentations at PASS this year on both indexes and statistics, and this info may influence those presentations.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398815 More actions · Answer 1

I haven't used either in a production environment yet. I've used both in tests.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Any findings/conclusions from testing?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Grant Fritchey SSC Guru Points: 398815 More actions · Answer 3

It's difficult to get the right set of conditions to see filtered indexes get chosen by the optimizer. Hints frequently don't help. It's VERY difficult to get filtered statistics to make a difference. It seems like these things are just a tad persnickety within the optimizer.

However, when I did see performance improvements from the filtered indexes, they were fairly solid. It's got value. Just determining when the value will be evident is not something I'm able to do at this point.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Thanks. I need to play with both, but since I don't have an actual prod server to play with, it's going to have to be in a lab based on a made-up database.

I know there's at least one bug around filtered index selection, I've got a connect item filed and apparently it'll be fixed in a future patch.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Sean Terry Old Hand Points: 363 More actions · Answer 5

Grant Fritchey (6/18/2009)
It's difficult to get the right set of conditions to see filtered indexes get chosen by the optimizer.

This has been my experience as well. I love the idea of filtered indexes, but haven't been able to put them to work.