Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Filtered Indexes - Simple Partitioning without Enterprise Edition? Expand / Collapse
Author
Message
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: Monday, June 23, 2014 2:12 AM
Points: 26, Visits: 360
Comments posted to this topic are about the item Filtered Indexes - Simple Partitioning without Enterprise Edition?


Post #1422411
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
Posted Thursday, February 21, 2013 7:27 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, May 14, 2014 7:57 AM
Points: 76, Visits: 342
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
Posted Thursday, February 21, 2013 7:58 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 10:01 AM
Points: 1,787, Visits: 5,697
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


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1422604
    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: Monday, June 23, 2014 2:12 AM
    Points: 26, Visits: 360
    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
    Posted Thursday, February 21, 2013 9:10 AM
    Grasshopper

    GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

    Group: General Forum Members
    Last Login: Wednesday, June 25, 2014 2:02 PM
    Points: 18, Visits: 241
    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
    Posted Thursday, February 21, 2013 9:26 AM
    Grasshopper

    GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

    Group: General Forum Members
    Last Login: Wednesday, August 27, 2014 7:21 AM
    Points: 24, Visits: 138
    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
    Posted Thursday, February 21, 2013 10:13 AM
    SSCrazy

    SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

    Group: General Forum Members
    Last Login: Friday, August 29, 2014 10:04 AM
    Points: 2,299, Visits: 1,356
    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
    Posted Thursday, February 21, 2013 1:22 PM
    SSCrazy

    SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

    Group: General Forum Members
    Last Login: Today @ 12:13 AM
    Points: 2,044, Visits: 3,060
    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)
    "In America, every man is innocent until proven broke!" Brant Parker
    Post #1422773
    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: Monday, June 23, 2014 2:12 AM
    Points: 26, Visits: 360
    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 12»»

    Permissions Expand / Collapse