September 19, 2011 at 3:25 am
Hi,
Can any one give me the recommendation to partition the table
in sql server 2005
1. After how many rows a table should be partitioned?
2. what should be the maximum size after which table should be partitioned?
3. Any other recommendation to partition the table from your end.
Thank u in advance,
Nikhil Desai
September 19, 2011 at 3:55 am
Both 1 and 2 have the same answer: 'It depends'
Depends why you are partitioning, what the usage of the table is, the access patterns of the DB and a few hundred other things. What's the reason you're considering partitioning?
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
September 19, 2011 at 5:20 am
nikhil.desai1 (9/19/2011)
1. After how many rows a table should be partitioned?2. what should be the maximum size after which table should be partitioned?
3. Any other recommendation to partition the table from your end.
I'm in agreement with Gail about #1 and #2.
In regards to #3 - buiding on Gail's comment, you do not partition because it's cool to have a partitioned table on your database. Most of the time you partition to help during Archive and Purge processes like purging the older partition of a particular transaction table on a OLTP system.
As a rule of thumbs, your partitioning strategy should help either Performance or Archiving/Purging - if your strategy doesn't help at least one of those processes forget about partitioning.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.September 19, 2011 at 5:25 am
PaulB-TheOneAndOnly (9/19/2011)
As a rule of thumbs, your partitioning strategy should help either Performance or Archiving/Purging - if your strategy doesn't help at least one of those processes forget about partitioning.
And partitioning for performance is not that common, nor usually that effective.
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
September 20, 2011 at 8:53 am
GilaMonster (9/19/2011)
PaulB-TheOneAndOnly (9/19/2011)
As a rule of thumbs, your partitioning strategy should help either Performance or Archiving/Purging - if your strategy doesn't help at least one of those processes forget about partitioning.And partitioning for performance is not that common, nor usually that effective.
Agreed.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply