Ideal table partition size per partition

  • npranj

    SSCrazy

    Points: 2323

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

  • Gail Shaw

    SSC Guru

    Points: 1004474

    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
  • npranj

    SSCrazy

    Points: 2323

    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.

  • Gail Shaw

    SSC Guru

    Points: 1004474

    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
  • npranj

    SSCrazy

    Points: 2323

    Ok. got you.. Thanks !

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

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