the short version of my question is: "During the merge of two partitions is the whole partitioned table being blocked. How can I access, for read&write, the part that is not affected by the merge?"

And the context:

I am working on quite common scenario - there is a production table that grows in linear way (a few hundreds rows every 5 minutes). But we do not have to keep historical data online, so we decided to use partitioning to "bite" the historical tail from the database and move it to another one.

There are many tutorials how to achieve this goal - create partition function, scheme, partition the table with it's indices, switch the partition to identical empty table and so on. And if I want to do this one more time, the "standard" way is to split the current partition and merge it's tail with the historical one.

Now what I want to end up with (example): The table T is partitioned by some integer column P. Lets say there are at the moment three partitions and three filegroups. The partition function is set to split by values 30 and 60. The current value of the "counter" that produces values of the P column is 50. So there are some rows in partition 1 (HISTORY) which is now read-only, some rows in partition 2 (PRIMARY) that gets the periodical inserts and empty partition 3 (FUTURE).

Let the value of the counter be 70. Now the partition 1 and 2 are in the same situation. They are both read-only. The inserts are incoming with P value belonging to partition 3 only. And now I want to merge partitions 1 and 2 and move the result to another database. But I can see that during the merge of these two partitions all operations with the table T are being blocked. Even if it is INSERT to the third partition or there is selection predicate matching the partitioning function. How can I set-up the scheme or something else to unblock SELECT/INSERT with P value from partition 3 only (that is out of the game with respect to the merge)?

  • A SPLIT or MERGE of a partition function will require a schema modification lock on all tables that use the partition schemes associated with the function. Consider switching the tail into a staging table partitioned using the same scheme, create a new function and scheme without the boundary, and then use CREATE INDEX...WITH DROP EXISTING to repartition the staging table tail. That will keep partition 3 online. A MERGE of the original scheme tail to remove the boundary will be fast since the partitions are empty but a brief schema modification locks will still be required. – Dan Guzman Jul 17 '17 at 11:53
  • @DanGuzman: Thank you for your suggestion! I end up with something very similar. I am going to post my (and your) workaround as an answer. – Jan Drozen Jul 19 '17 at 7:49
up vote 1 down vote accepted

I solved this situation using a simple workaround (very similar to DanGuzman's comment).

Merging partitions is in-blink-of-an-eye operation if and only if both partitions are empty. When the partition function is defined as RANGE RIGHT, then during the merge the leftmost partition will eat the other on the right side. Due to this all records have to be moved between partitions filegroups. The workaround is to rebuild or simply drop and recreate the staging table - the table with exactly the same schema as the source partitioned table. The only difference is the filegroup of the nonpartitioned table and it's indices must match filegroup of the partition that is going to be merged. This reduces both of operations - partition switching and partition merge to be metadata operation only. QED.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.