Post reply

Will partitioning help?

  • May 26, 2012 at 4:37 am

    #1493478

    Gullimeel (5/26/2012)

    --------------------------------------------------------------------------------

    Next time I wont save the typing efforts because it has caused much more typing then i thought it would save me(not a nice optimization decision) so that discussion stays around what it has been started for.

    So did you see the TOP operators and the BACKWARD scans?

    I do not want to go that path anymore. I was not testing the performance of max/min or top 1.I used wrong operator to show what i wanted to say.Wrong choice on my side:).

    and Yes I can see the opertors.

    GulliMeel

    Finding top n Worst Performing queries[/url]
    Improve the performance of Merge Join(special case)
    How to Post Performance Problem -Gail Shaw[/url]

  • May 26, 2012 at 4:51 am

    #1493479

    Gullimeel (5/26/2012)

    I do not want to go that path anymore. I was not testing the performance of max/min or top 1.I used wrong operator to show what i wanted to say.Wrong choice on my side:). and Yes I can see the opertors.

    And the partition BACKWARD scans that you said don't exist? You saw them too? Reproduced below for everyone else.

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

  • May 26, 2012 at 4:59 am

    #1493480

    Gullimeel (5/26/2012)

    --------------------------------------------------------------------------------

    I do not want to go that path anymore. I was not testing the performance of max/min or top 1.I used wrong operator to show what i wanted to say.Wrong choice on my side:). and Yes I can see the opertors.

    And the BACKWARD scans that you said don't exist? You saw them too?

    There we go...I was explaining the reason why the max was not performing well because it was not doing backward scans as it was able to do in unpartitioned table. With workaround offcourse it is doing backward scans and i can see BACKWARD scan too;-).

    Again I am saying I was not looking at the issue related to the bug with partitioned index but wanted to show the adavtages of partitioning when you have full index scans.My use of max took the whole discussion to the bug side.I should not have used that all.

    GulliMeel

    Finding top n Worst Performing queries[/url]
    Improve the performance of Merge Join(special case)
    How to Post Performance Problem -Gail Shaw[/url]

  • May 26, 2012 at 5:09 am

    #1493483

    GilaMonster (5/22/2012)

    andersg98 (5/22/2012)

    Yes. The concept is called 'partition elimination'. Especially when scanning the table for a date range, the optimizer will realize it only has to scan the current year partition.

    That's valid if you're converting a full table scan into a scan of one or more partitions. However for that to be the case it means that there are no useful indexes for that query and a better approach than partitioning would be to evaluate and add/modify indexes.

    This.

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

  • May 26, 2012 at 5:23 am

    #1493487

    GilaMonster (5/22/2012)

    --------------------------------------------------------------------------------

    andersg98 (5/22/2012)

    --------------------------------------------------------------------------------

    Yes. The concept is called 'partition elimination'. Especially when scanning the table for a date range, the optimizer will realize it only has to scan the current year partition.

    That's valid if you're converting a full table scan into a scan of one or more partitions. However for that to be the case it means that there are no useful indexes for that query and a better approach than partitioning would be to evaluate and add/modify indexes.

    This

    I mentioned non clustered index scans :). Related to table scan I have supported what Gila has mentioned.

    I have seen a case where clustered index(it was not distinct but had quite a lot of distinct values) was as effective as the partitioning ( almost all queries had the clustered index key as sarg). But still used partitioning because DBA's sugegsted that maintenance would be easier. But this decision was based on more like maintenance rather than performance benefit of it.

    GulliMeel

    Finding top n Worst Performing queries[/url]
    Improve the performance of Merge Join(special case)
    How to Post Performance Problem -Gail Shaw[/url]

  • May 26, 2012 at 5:29 am

    #1493488

    Gullimeel (5/26/2012)

    GilaMonster (5/22/2012)

    --------------------------------------------------------------------------------

    andersg98 (5/22/2012)

    --------------------------------------------------------------------------------

    Yes. The concept is called 'partition elimination'. Especially when scanning the table for a date range, the optimizer will realize it only has to scan the current year partition.

    That's valid if you're converting a full table scan into a scan of one or more partitions. However for that to be the case it means that there are no useful indexes for that query and a better approach than partitioning would be to evaluate and add/modify indexes.

    This

    I mentioned non clustered index scans :).

    Same difference. If you have scans, then either you need the entire table (in which case partitioning won't help) or you can tune the indexes (or queries) to make them more efficient.

    Sure, partitioning may help when you have scans (table or index) cause by poor indexing, but tuning the indexing will quite likely get you the same or better results in the vast majority of cases.

    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

  • May 26, 2012 at 5:43 am

    #1493489

    Gullimeel (5/26/2012)

    --------------------------------------------------------------------------------

    GilaMonster (5/22/2012)

    --------------------------------------------------------------------------------

    andersg98 (5/22/2012)

    --------------------------------------------------------------------------------

    Yes. The concept is called 'partition elimination'. Especially when scanning the table for a date range, the optimizer will realize it only has to scan the current year partition.

    That's valid if you're converting a full table scan into a scan of one or more partitions. However for that to be the case it means that there are no useful indexes for that query and a better approach than partitioning would be to evaluate and add/modify indexes.

    This

    I mentioned non clustered index scans :).

    Same difference. If you have scans, then either you need the entire table (in which case partitioning won't help) or you can tune the indexes (or queries) to make them more efficient.

    Sure, partitioning may help when you have scans (table or index) cause by poor indexing, but tuning the indexing will quite likely get you the same or better results in the vast majority of cases.

    As mentioned by Paul earlier alternate for these nonclustered index scan(full) is Filtered indexes but again what solution should be implemneted is dependent on your requirement(not just performance).

    If i were in such a position I would do following:

    Perform a small test for alternate methods using the most common load Iincluding dmls/maintenance) and see which one is performing better.

    GulliMeel

    Finding top n Worst Performing queries[/url]
    Improve the performance of Merge Join(special case)
    How to Post Performance Problem -Gail Shaw[/url]

  • May 26, 2012 at 6:03 am

    #1493491

    Gullimeel (5/26/2012)

    If i were in such a position I would do following:

    Perform a small test for alternate methods using the most common load Iincluding dmls/maintenance) and see which one is performing better.

    Thanks so much for your valuable insight :rolleyes:

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

  • May 26, 2012 at 6:47 am

    #1493494

    Gullimeel (5/26/2012)

    As mentioned by Paul earlier alternate for these nonclustered index scan(full) is Filtered indexes

    Or a unfiltered nonclustered index that supports the query. Or a change to the query so that it can use an existing index or ... There are many possible ways to optimise such a query. Partitioning for performance optimisation would not be my first, second or third choice, it's a lot of work and the other methods are very likely to give as good a return for a lot less effort.

    Please note, no one's saying partitoning's not good for maintenance or data loading, etc, we're saying that it's not a good choice if someone is trying to improve performance of a query.

    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 9 posts - 31 through 38 (of 38 total)

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