My blog has moved to sqlblogcasts.com/blogs/simons
The full version of this post can be found here., http://sqlblogcasts.com/blogs/simons/How to speed up partition merging
The new RSS feed is here
The key to performance with partition management is to
deal with empty partitions. That way all you have is meta data changes and no
data has to be copied.
We recently encountered a situation where we had two partitions and wanted to
merge them. In a merge you have 3 boundaries the left one, the middle one and
the right one. In doing a merge you are going to get rid of the middle one and
have 1 partition that is bounded by the left and right values. The partition on
the left of the middle boundary was empty and the one on the right wasn't. We
were using RIGHT partitioning so the partition value for the middle boundary was
included in the RIGHT hand partition. We expected this to be very
quick. The engine you take the RIGHT hand partition and use it for the new
partition.
... To read the complete version of this post go here, http://sqlblogcasts.com/blogs/simons/How to speed up partition merging