2

I was reading ALTER PARTITION FUNCTION best practices which says -

Always keep empty partitions at both ends of the partition range to guarantee that the partition split (before loading new data) and partition merge (after unloading old data) do not incur any data movement. Avoid splitting or merging populated partitions. This can be extremely inefficient, as this may cause as much as four times more log generation, and may also cause severe locking.

Questions :

  • Is it sufficient to ensure that there will be no data movement by ensuring that the new boundary corresponds to a future date or do I really need the empty partition ?
  • I think certain optimizations are done if the partition is empty, but is it correct to say that the major cost is in the data movement itself?
  • Is there anything new in SQL 2014 that could help out (we are moving from 2008 to 2014 .. yes I am aware that SQL Server 2019 is out ..but we will eventually get there) ?

I saw a lot on Managed Lock Priority (e.g. https://blogs.technet.microsoft.com/dataplatforminsider/2013/08/16/improved-application-availability-during-online-operations-in-sql-server-2014/); however, that seems to apply only to Table Partition Switch and Online Index Rebuild, but not to an ALTER PARTITITON FUNCTION.

1

1 Answer 1

3

Is it sufficient to ensure that there will be no data movement by ensuring that the new boundary corresponds to a future date or do I really need the empty partition ?

If your partition function is RANGE RIGHT, then yes. A partition split moves rows in the existing partition that belong in the new partition. The new partition is the partition that contains the new boundary value. So in RANGE RIGHT if the new boundary value is after all the rows in the table, no rows need be moved.

Hopefully you choose RANGE RIGHT to begin with so you don't have to have boundary values like 2015-12-31 11:59:59:999.

Depending on the indexes, the head partition may need to be scanned to discover that no rows need to be moved.

2
  • Thanks ! Any new stuff in sql 2014 that is geared towards table partitioning (i could not find and so have to ask) ?
    – Kin Shah
    Sep 28, 2018 at 21:02
  • The big deal is Columnstores, but they are only partially supported in SQL 2014. Not directly related to partitioning, but applicable to most of the same scenarios. Sep 28, 2018 at 22:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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