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
.