Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Filtered Indexes - Simple Partitioning without Enterprise Edition?


Filtered Indexes - Simple Partitioning without Enterprise Edition?

Author
Message
maneffa
maneffa
SSC Rookie
SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)

Group: General Forum Members
Points: 36 Visits: 373
Comments posted to this topic are about the item Filtered Indexes - Simple Partitioning without Enterprise Edition?



amal7us
amal7us
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 11
It will add the overhead of the index i.e. inserts will be slower than in partitioned tables
James A Skipwith
James A Skipwith
SSC-Enthusiastic
SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)

Group: General Forum Members
Points: 198 Visits: 658
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]
mister.magoo
mister.magoo
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2697 Visits: 7851
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 :-P

MM


select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);




  • 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

  • maneffa
    maneffa
    SSC Rookie
    SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)

    Group: General Forum Members
    Points: 36 Visits: 373
    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



    rice.tx
    rice.tx
    Grasshopper
    Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

    Group: General Forum Members
    Points: 20 Visits: 255
    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.
    davidturpin
    davidturpin
    SSC Rookie
    SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)

    Group: General Forum Members
    Points: 32 Visits: 181
    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+.
    Miles Neale
    Miles Neale
    SSCrazy
    SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

    Group: General Forum Members
    Points: 2752 Visits: 1694
    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!
    ScottPletcher
    ScottPletcher
    SSCarpal Tunnel
    SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

    Group: General Forum Members
    Points: 4451 Visits: 6833
    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)

    Prosecutor James Blackburn, in closing argument in the "Fatal Vision" murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."
    maneffa
    maneffa
    SSC Rookie
    SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)SSC Rookie (36 reputation)

    Group: General Forum Members
    Points: 36 Visits: 373
    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.



    Go


    Permissions

    You can't post new topics.
    You can't post topic replies.
    You can't post new polls.
    You can't post replies to polls.
    You can't edit your own topics.
    You can't delete your own topics.
    You can't edit other topics.
    You can't delete other topics.
    You can't edit your own posts.
    You can't edit other posts.
    You can't delete your own posts.
    You can't delete other posts.
    You can't post events.
    You can't edit your own events.
    You can't edit other events.
    You can't delete your own events.
    You can't delete other events.
    You can't send private messages.
    You can't send emails.
    You can read topics.
    You can't vote in polls.
    You can't upload attachments.
    You can download attachments.
    You can't post HTML code.
    You can't edit HTML code.
    You can't post IFCode.
    You can't post JavaScript.
    You can post emoticons.
    You can't post or upload images.

    Select a forum

































































































































































    SQLServerCentral


    Search