Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Nigel Maneffa » Filtered Indexes - Simple Partitioning...
Add to briefcase
16 posts, Page 1 of 2
12»»

Filtered Indexes - Simple Partitioning without Enterprise Edition? Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
maneffa
Posted Thursday, February 21, 2013 12:01 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 10:40 AM
Points: 26, Visits: 335
Comments posted to this topic are about the item Filtered Indexes - Simple Partitioning without Enterprise Edition?


Post #1422411
amal7us
Posted Thursday, February 21, 2013 5:22 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, February 22, 2013 4:03 AM
Points: 1, Visits: 11
It will add the overhead of the index i.e. inserts will be slower than in partitioned tables
Post #1422507
James A Skipwith
Posted Thursday, February 21, 2013 7:27 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, February 22, 2013 8:20 AM
Points: 59, Visits: 276
Nigel, certainly a good quick method of reducing IO in this situation with no code changes. Bear in mind that statistics are NOT automatically maintained on filtered indexes and you will have to add maintenance to do this yourself, otherwise your performance gains will eventually slip away.

James
MCM [@TheSQLPimp]
Post #1422579
mister.magoo
Posted Thursday, February 21, 2013 7:58 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 9:21 AM
Points: 1,300, Visits: 3,893
Thanks for the article, my only criticism being the lack of examples or links to further reading, which would have been nice.

But, I can Google, so no biggie

MM
Post #1422604
maneffa
Posted Thursday, February 21, 2013 8:19 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 10:40 AM
Points: 26, Visits: 335
That is correct, and one of the limitations of this solution. In my scenario the maintenance of the index does not add a lot of overhead but I could see other examples where it could be significant.

Thanks
Nigel


Post #1422622
rice.tx
Posted Thursday, February 21, 2013 9:10 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, May 08, 2013 9:39 AM
Points: 15, Visits: 201
We have a very similar situation, and are looking forward to upgrading and making use of filtered indexes.

It's not clear to me... did you try using filtered indexes with just the "status" field, and still had problems with complex queries? Or were the problems you experienced in prior versions, where the "status" index was a regular (non-filtered) index.

Thanks.
Post #1422660
Dave Turpin
Posted Thursday, February 21, 2013 9:26 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: 2 days ago @ 8:10 AM
Points: 24, Visits: 118
In my opinion, filtered indexes are one of the unsung heroes of 2008+. They can significantly reduce IO when the column is part of an oft-used predicate.

As previously mentioned, relying on autostats to maintain the statistics will only result in threshold changes at the TABLE level, not the column level. For slowly changing, large, historical tables, statistics will only be an issue over time. More than likely, if you have a defrag plan on your indexes, the stats on the index will be updated by the index rebuild process.

Thanks for re-enforcing my belief patterns.

We use enterprise almost exclusively but the STANDARD edition point is worth the reminder. PARTITIONING is enterprise+.
Post #1422673
Miles Neale
Posted Thursday, February 21, 2013 10:13 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, May 20, 2013 9:51 AM
Points: 1,891, Visits: 936
Thanks for the article and talking about the problem solving as you walked through this. We have done like things and have built these to construct "covered queries" where the index contains that fields we want to use. Again it adds to overhead overall but the speed of getting the data where we read far more than we write has sped up the major functions of the system.

Keep up the good work!

Not all gray hairs are Dinosaurs!
Post #1422696
ScottPletcher
Posted Thursday, February 21, 2013 1:22 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 3:56 PM
Points: 1,324, Visits: 1,778
Did you try just putting the Status column only in the index? SQL can still use that index to do a key lookup back to the main table. Hopefully the number of rows in Active status would be limited enough to allow SQL to do that.

Or did you try that and find that SQL would then not use the index but do a full scan instead?!

SQL DBA,SQL Server MVP('07, '08, '09)
One man with courage makes a majority. Andrew Jackson
Post #1422773
maneffa
Posted Friday, February 22, 2013 3:19 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 10:40 AM
Points: 26, Visits: 335
rice.tx (2/21/2013)
We have a very similar situation, and are looking forward to upgrading and making use of filtered indexes.

It's not clear to me... did you try using filtered indexes with just the "status" field, and still had problems with complex queries? Or were the problems you experienced in prior versions, where the "status" index was a regular (non-filtered) index.

Thanks.


Hi there, originally the status field was a normal index, and then I started experimenting with a filtered index and a few covering fields, and eventually came to the conclusion that adding all the columns would be highly beneficial in this scenario. This was due to the steady increase in disk IO over the years starting to create minor performance issues. Although I show a very simple example the real world queries are actually very complex but the common theme was very frequently that I was looking to take all the ACTIVE customer orders and look at the ACTIVE order routing records for those orders so if I had 'tables' of just the active orders the query optimiser would have a much simpler time without thinking about bookmark lookups etc. I hope this helps.


Post #1422952
« Prev Topic | Next Topic »

Add to briefcase
16 posts, Page 1 of 2
12»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.