locked
table partition RRS feed

  • Question

  • hi,

    i have data that i am partitioning on date field, at all times, i want current 2 months in a partition, prior twi months in another partition and rest all in other partition, pls guide on how to accomplish this dynamically.

    thanks

    nik

    Friday, November 1, 2013 10:18 PM

Answers

  • I have a fact table that is huge enough and causing the processing time to go very high, so I want the current rwo months in one partition for that table and the prior months in another partition and everything else in third partition.

    Partitioning is not going to help you with that scenario. Partitioning is not a performance feature, it is more aimed to simplify management. From a performance perspective, partitioning is often detrimental, except for one operation: quickly get rid of old data.

    The kind of partitioning you describe can make sense. One scenario is that most queries goes against the most recent data, and you want these to be superfast, so you want to put that data on FusionIO cards. Unfortunately, your budget does not suffice to put the entire table there, why you need to put older data on spinning disks. Another scenario is that you want to put the old data on a read-only filegroup to reduce backup times.

    But for the scenario you describe, partitioning is not going to help you, but may make matters worse.

    Instead, you need to investigate your indexes, and make sure that they agree with the query pattern you users use.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Fanny Liu Monday, November 4, 2013 9:23 AM
    • Marked as answer by Fanny Liu Thursday, November 14, 2013 1:18 AM
    Saturday, November 2, 2013 11:15 AM

All replies

  • Why do you want this? It is doable, and it may make sense. However, it implies that you want to physically move database between partitions, and normally you partition tables because you don't want to move data around.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, November 1, 2013 11:32 PM
  • I have a fact table that is huge enough and causing the processing time to go very high, so I want the current rwo months in one partition for that table and the prior months in another partition and everything else in third partition.

    I want to do this dynamically on the fact table.

    how do I accomplish to automate the above scenario , so that for evry month , the current partition of the table has current two months and the second partition has second two months and remaining else in third partition, thanks for the guidance.

    nik

    Saturday, November 2, 2013 12:26 AM
  • I have a fact table that is huge enough and causing the processing time to go very high, so I want the current rwo months in one partition for that table and the prior months in another partition and everything else in third partition.

    Partitioning is not going to help you with that scenario. Partitioning is not a performance feature, it is more aimed to simplify management. From a performance perspective, partitioning is often detrimental, except for one operation: quickly get rid of old data.

    The kind of partitioning you describe can make sense. One scenario is that most queries goes against the most recent data, and you want these to be superfast, so you want to put that data on FusionIO cards. Unfortunately, your budget does not suffice to put the entire table there, why you need to put older data on spinning disks. Another scenario is that you want to put the old data on a read-only filegroup to reduce backup times.

    But for the scenario you describe, partitioning is not going to help you, but may make matters worse.

    Instead, you need to investigate your indexes, and make sure that they agree with the query pattern you users use.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Fanny Liu Monday, November 4, 2013 9:23 AM
    • Marked as answer by Fanny Liu Thursday, November 14, 2013 1:18 AM
    Saturday, November 2, 2013 11:15 AM