September 21, 2008 at 5:39 pm
I am part of a team that is considering implementing a Table Partition in order to achieve some performance gains. I think that I am on board, with relatively little work, we can implement this and get some benefits (of course results from testing are yet to be determined), but compared to an archiving strategy or a full blown data warehouse, it should be easy to implement.
My biggest question is related to the downsides. Are there any areas of concern that we should be looking at if we choose a Table Partition strategy?
Thanks,
Scott
September 21, 2008 at 5:50 pm
If you are running a query that crosses partition boundaries, there is a small performance overhead, compared to the same query against a non-partitioned table.
As I understand it the main performance benefits come from the ability to load and delete partitions quickly and also to back them up separately.
September 22, 2008 at 5:29 am
AS previously posted, there are not a lot of down sides. You need enterprise SQL, so make sure you have ponied up for the licensing. The possible performance hits are pretty low unless you split tables into a lot of partitions. Do the same work you would with moving indexes and tables into different file groups - make the best use of separate physical disks.
September 22, 2008 at 6:11 am
Thanks for the input. We do have Enterprise edition, so that is why we are considering this option. As we design the partitions, we'll try to keep the advice about crossing partitions in mind. It'll will be a date based partition, likely by year.
Thanks,
Scott
September 22, 2008 at 6:33 am
there are some known issues with parallelism. go through this post - http://blogs.msdn.com/sqlcat/archive/2005/11/30/498415.aspx
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply