Ideal table partition size per partition

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » Ideal table partition size per partition

Ideal table partition size per partition

Rate Topic

Display Mode

Topic Options

Author

Message

npranj

Posted Monday, February 11, 2013 10:10 PM

SSC Rookie

Group: General Forum Members
Last Login: Friday, May 17, 2013 1:15 AM
Points: 36, Visits: 254

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

Post #1418745

GilaMonster

Posted Tuesday, February 12, 2013 12:39 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:37 PM
Points: 37,687, Visits: 29,944

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 2008, MVP
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

Post #1418773

npranj

Posted Tuesday, February 12, 2013 2:09 AM

SSC Rookie

Group: General Forum Members
Last Login: Friday, May 17, 2013 1:15 AM
Points: 36, Visits: 254

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.

Post #1418804

GilaMonster

Posted Tuesday, February 12, 2013 2:20 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:37 PM
Points: 37,687, Visits: 29,944

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 2008, MVP
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

Post #1418809

npranj

Posted Tuesday, February 12, 2013 2:25 AM

SSC Rookie

Group: General Forum Members
Last Login: Friday, May 17, 2013 1:15 AM
Points: 36, Visits: 254

Ok. got you.. Thanks !

Post #1418811

« Prev Topic | Next Topic »

Permissions