May 23, 2013 at 12:48 pm
Current partitioning strategy does not restrict growth within a single partition where we already see 576,114,055 records and growing. Operations on that specific partition are taking unacceptable amount of time. We'd like to re-partition the table with something more manageable but the problem is that the amount of locking that is going to take to merge all partitions into one, switching it out to a non-partition table and then re-partitioning the new table, is unpredictably long (can't simulate production load).
Looks like this is the case with just 3-4 partitions, others are 3-5mm range. Any suggestions on how to address those few partitions without re-partitioning the entire table? Or if we have to re-partition, is there a better way than the one proposed here?
Thanks
May 23, 2013 at 1:45 pm
Lexa (5/23/2013)
Current partitioning strategy does not restrict growth within a single partition where we already see 576,114,055 records and growing. Operations on that specific partition are taking unacceptable amount of time. We'd like to re-partition the table with something more manageable but the problem is that the amount of locking that is going to take to merge all partitions into one, switching it out to a non-partition table and then re-partitioning the new table, is unpredictably long (can't simulate production load).Looks like this is the case with just 3-4 partitions, others are 3-5mm range. Any suggestions on how to address those few partitions without re-partitioning the entire table? Or if we have to re-partition, is there a better way than the one proposed here?
If the issue is performance then partitioning per-se is not going to solve it - a sound indexing strategy and well written queries do.
Either way, ask yourself "why was this table partitioned this way?" and remember, table partitioning is all about administration, not performance.
_____________________________________
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.May 23, 2013 at 2:53 pm
PaulB-TheOneAndOnly (5/23/2013)
Lexa (5/23/2013)
Current partitioning strategy does not restrict growth within a single partition where we already see 576,114,055 records and growing. Operations on that specific partition are taking unacceptable amount of time. We'd like to re-partition the table with something more manageable but the problem is that the amount of locking that is going to take to merge all partitions into one, switching it out to a non-partition table and then re-partitioning the new table, is unpredictably long (can't simulate production load).Looks like this is the case with just 3-4 partitions, others are 3-5mm range. Any suggestions on how to address those few partitions without re-partitioning the entire table? Or if we have to re-partition, is there a better way than the one proposed here?
If the issue is performance then partitioning per-se is not going to solve it - a sound indexing strategy and well written queries do.
Either way, ask yourself "why was this table partitioned this way?" and remember, table partitioning is all about administration, not performance.
Looks like Microsoft disagrees with you http://msdn.microsoft.com/en-us/library/ms177411(v=sql.105).aspx
May 23, 2013 at 3:15 pm
Lexa (5/23/2013)
PaulB-TheOneAndOnly (5/23/2013)
Lexa (5/23/2013)
Current partitioning strategy does not restrict growth within a single partition where we already see 576,114,055 records and growing. Operations on that specific partition are taking unacceptable amount of time. We'd like to re-partition the table with something more manageable but the problem is that the amount of locking that is going to take to merge all partitions into one, switching it out to a non-partition table and then re-partitioning the new table, is unpredictably long (can't simulate production load).Looks like this is the case with just 3-4 partitions, others are 3-5mm range. Any suggestions on how to address those few partitions without re-partitioning the entire table? Or if we have to re-partition, is there a better way than the one proposed here?
If the issue is performance then partitioning per-se is not going to solve it - a sound indexing strategy and well written queries do.
Either way, ask yourself "why was this table partitioned this way?" and remember, table partitioning is all about administration, not performance.
Looks like Microsoft disagrees with you http://msdn.microsoft.com/en-us/library/ms177411(v=sql.105).aspx
:doze: read harder, do not stop where you "think" you got a good argument 😀
_____________________________________
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.May 24, 2013 at 7:22 am
PaulB-TheOneAndOnly (5/23/2013)
Lexa (5/23/2013)
PaulB-TheOneAndOnly (5/23/2013)
Lexa (5/23/2013)
Current partitioning strategy does not restrict growth within a single partition where we already see 576,114,055 records and growing. Operations on that specific partition are taking unacceptable amount of time. We'd like to re-partition the table with something more manageable but the problem is that the amount of locking that is going to take to merge all partitions into one, switching it out to a non-partition table and then re-partitioning the new table, is unpredictably long (can't simulate production load).Looks like this is the case with just 3-4 partitions, others are 3-5mm range. Any suggestions on how to address those few partitions without re-partitioning the entire table? Or if we have to re-partition, is there a better way than the one proposed here?
If the issue is performance then partitioning per-se is not going to solve it - a sound indexing strategy and well written queries do.
Either way, ask yourself "why was this table partitioned this way?" and remember, table partitioning is all about administration, not performance.
Looks like Microsoft disagrees with you http://msdn.microsoft.com/en-us/library/ms177411(v=sql.105).aspx
:doze: read harder, do not stop where you "think" you got a good argument 😀
I don't know what experience you have with partitioning. Working with billion record tables made a significant query performance improvements for us after partitioning.
May 24, 2013 at 7:30 am
That just mean you had bad indexes before, partition just helped you by chance.
To improve performance, partitioning need to be done with good indexing strategy as Paul told you.
Most of the time good indexing alone would do the trick.
Now for your problem, on what kind of key are you partitioning? And how would you like to re-partition it, same key but more value?
Do all your partition have the same amount of rows of are there big differences?
May 24, 2013 at 7:43 am
Partitioning can clearly improve query performance in a different way than indexing can. Partition elimination can be vital to query performance with very large tables, particularly in Data Warehousing, where scans are common and query paths aren't fully defined, but there are some clear sub-groups of data that will generally occur in predicates.
Although phrased in the absolute, which isn't really correct, what I think Paul is cautioning against is the view some people have of partitioning being a universal panacea to performance, where, especially in OLTP, it's really not likely to help much compared to appropriate indexing. In fact, it can significantly hinder if not implemented well and it has far more utility in terms of administration/archiving etc. than it does as a performance lever.
May 24, 2013 at 8:00 am
HowardW, completely agree.
I have a partition in place that has unrestricted growth (flaw in partitioning strategy that came from unforeseen business case after the fact), which in my case has 590 million records and continues to grow. Operations on that partition experience performance issues. Inserts, selects, index re-organization or rebuild on that specific partition are taking much more resources and time that if the partition was limited to only 20 million records.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply