Post reply

Ideal table partition size per partition

  • February 11, 2013 at 10:10 pm

    #270621

    What should be the ideal table partition size (per partition) in a DB Server having 80 CPUs and 1 TB ram from performance perspective?

  • February 12, 2013 at 12:39 am

    #1586853

    Partitioning is not about performance. It's mostly about data management, fast loads, fast removal of data, compression of different partitions, index rebuilds at the partition level.

    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

  • February 12, 2013 at 2:09 am

    #1586879

    Thanks yes I agree..

    I am trying to design a table partition.

    I have identified the Partition Key (Date) and for creating the partition function - the range.

    I am trying to understand how I could define the range best.

    For this -

    1. I have found out the Size Per Row for the table.

    2. On teh basis of assumptions (from business) on the data growth in future, I am trying to find out the best range.

    3. I am calculating the projected size in future for each month and trying to sum up the size for months grouped together for which I can create range. This grouping could be 30GB each or 40 GB each or 50 GB each etc etc..

    For this I need help to understand how much of a per partition size would be OK for the server that has 1 TB RAM and 80 CPUs. This will help me define the range.

  • February 12, 2013 at 2:20 am

    #1586883

    Again, partitioning is not about performance. You define the ranges based on your business requirements for loading and removing data, that will guide your partition scheme and function. There's no 'ideal' size for a partition, just like there's no 'ideal' size for a table.

    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

  • February 12, 2013 at 2:25 am

    #1586885

    Ok. got you.. Thanks !

Viewing 5 posts - 1 through 4 (of 4 total)

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