1

I'm practicing splitting a partition function on a QA box to add new partitions for the upcoming calendar year. I'm running SQL Server Enterprise edition, patch level 12.0.4100. Here is the current function definition...

CREATE PARTITION FUNCTION [PF_UTC](datetime2(7)) AS RANGE LEFT 
FOR VALUES (N'2012-01-01T00:00:00.000', N'2012-04-01T00:00:00.000', 
N'2012-07-01T00:00:00.000', N'2012-10-01T00:00:00.000', 
N'2013-01-01T00:00:00.000', N'2013-04-01T00:00:00.000', 
N'2013-07-01T00:00:00.000', N'2013-10-01T00:00:00.000', 
N'2014-01-01T00:00:00.000', N'2014-04-01T00:00:00.000', 
N'2014-07-01T00:00:00.000', N'2014-10-01T00:00:00.000', 
N'2015-01-01T00:00:00.000', N'2015-04-01T00:00:00.000', 
N'2015-07-01T00:00:00.000', N'2015-10-01T00:00:00.000', 
N'2016-01-01T00:00:00.000', N'2016-04-01T00:00:00.000')

Three partition schemes were defined at the time of function creation but only the first one is utilized with data files added.

CREATE PARTITION SCHEME [PS_UTC_Clustered] AS PARTITION [PF_UTC] TO (
    [Partitioned_Pre2012_Clustered]
    ,[Partitioned_2012Q1_Clustered]
    ,[Partitioned_2012Q2_Clustered]
    ,[Partitioned_2012Q3_Clustered]
    ,[Partitioned_2012Q4_Clustered]
    ,[Partitioned_2013Q1_Clustered]
    ,[Partitioned_2013Q2_Clustered]
    ,[Partitioned_2013Q3_Clustered]
    ,[Partitioned_2013Q4_Clustered]
    ,[Partitioned_2014Q1_Clustered]
    ,[Partitioned_2014Q2_Clustered]
    ,[Partitioned_2014Q3_Clustered]
    ,[Partitioned_2014Q4_Clustered]
    ,[Partitioned_2015Q1_Clustered]
    ,[Partitioned_2015Q2_Clustered]
    ,[Partitioned_2015Q3_Clustered]
    ,[Partitioned_2015Q4_Clustered]
    ,[Partitioned_2016Q1_Clustered]
    ,[Partitioned_2016Q2_Clustered]
    )

CREATE PARTITION SCHEME [PS_UTC_NonClustered] AS PARTITION [PF_UTC] TO (
    [Partitioned_Pre2012_NonClustered]
    ,[Partitioned_2012Q1_NonClustered]
    ,[Partitioned_2012Q2_NonClustered]
    ,[Partitioned_2012Q3_NonClustered]
    ,[Partitioned_2012Q4_NonClustered]
    ,[Partitioned_2013Q1_NonClustered]
    ,[Partitioned_2013Q2_NonClustered]
    ,[Partitioned_2013Q3_NonClustered]
    ,[Partitioned_2013Q4_NonClustered]
    ,[Partitioned_2014Q1_NonClustered]
    ,[Partitioned_2014Q2_NonClustered]
    ,[Partitioned_2014Q3_NonClustered]
    ,[Partitioned_2014Q4_NonClustered]
    ,[Partitioned_2015Q1_NonClustered]
    ,[Partitioned_2015Q2_NonClustered]
    ,[Partitioned_2015Q3_NonClustered]
    ,[Partitioned_2015Q4_NonClustered]
    ,[Partitioned_2016Q1_NonClustered]
    ,[Partitioned_2016Q2_NonClustered]
    )
CREATE PARTITION SCHEME [PS_UTC_Text] AS PARTITION [PF_UTC] TO (
    [Partitioned_Pre2012_Text]
    ,[Partitioned_2012Q1_Text]
    ,[Partitioned_2012Q2_Text]
    ,[Partitioned_2012Q3_Text]
    ,[Partitioned_2012Q4_Text]
    ,[Partitioned_2013Q1_Text]
    ,[Partitioned_2013Q2_Text]
    ,[Partitioned_2013Q3_Text]
    ,[Partitioned_2013Q4_Text]
    ,[Partitioned_2014Q1_Text]
    ,[Partitioned_2014Q2_Text]
    ,[Partitioned_2014Q3_Text]
    ,[Partitioned_2014Q4_Text]
    ,[Partitioned_2015Q1_Text]
    ,[Partitioned_2015Q2_Text]
    ,[Partitioned_2015Q3_Text]
    ,[Partitioned_2015Q4_Text]
    ,[Partitioned_2016Q1_Text]
    ,[Partitioned_2016Q2_Text]
    )

I'm not planning on creating 2016Q3 and 2016Q4 quarters as I don't want to incur the data movement between the filegroups. I've elected to start with 1/1/2017 and create a 2017Q1 filegroup. I execute the following, anticipating that it'll be a quick meta modification.

--CREATE 2017Q1 FG
USE [master];

ALTER DATABASE [JMQ] ADD FILEGROUP [JMQ_2017Q1];

--ADD 2017Q1 Data Files
ALTER DATABASE [JMQ] ADD FILE (
    NAME = N'JMQ_2017Q1_01'
    ,FILENAME = N'M:\DATA\mssql\data\JMQ_2017Q1_01.ndf'
    ,SIZE = 1024000 KB
    ,FILEGROWTH = 1024000 KB
    ) TO FILEGROUP [JMQ_2017Q1]

--ALTER PARTITION SCHEME NEXT USED FOR NEW FG
USE [JMQ];

ALTER PARTITION SCHEME PS_UTC_Clustered NEXT USED [JMQ_2017Q1];

USE [JMQ];

ALTER PARTITION SCHEME PS_UTC_NonClustered NEXT USED [JMQ_2017Q1];

USE [JMQ];

ALTER PARTITION SCHEME PS_JMQ_UTC_Text NEXT USED [JMQ_2017Q1];

--ALTER PARTITION FUNCTION TO SPLIT RANGE ON 1/1/17 00:00:00
USE [JMQ];

ALTER PARTITION FUNCTION PF_UTC () SPLIT RANGE ('2017-01-01 00:00:00')

But right now, it's been running for 90 minutes. I'm watching via Spotlight that the new data file is being populated. I spot-check the UTC dates on all the tables in the DB in Prod and confirm nothing is dated after 1/1/2017. I can understand the engine needing to seek/scan indexes to confirm nothing has to move to the FG, but if no records are qualified to move, why all the data movement?

Improve this question

2 Answers 2

Reset to default
2

Have a look at Dan Guzman's excellent post on Sql Server partitioning - specifically what happens during a LEFT RANGE split.

Here are the highlights:

The actions performed by a SPLIT of a RANGE LEFT partition function:

  • Identify existing partition to be split, which is the one that contains the new boundary (or the last partition if no existing boundaries are higher than the one being added)

  • Add the new boundary to the partition function, maintaining boundary order and incrementing subsequent partition numbers

  • Create a new partition to the left of the existing one on the NEXT USED filegroup of each partition scheme that uses the function

  • For each table/index using the affected partition scheme(s), move rows from the existing split partition that are less than or equal to the new boundary into the newly created partition on the left

Improve this answer
1
  • Scott, this was most helpful. I like Dan's walkthrough of the RANGE LEFT split; it matches my use case. Per my comments to Max, I got teased by my earlier success with a RANGE RIGHT split. At least I'm running this on a QA box so I can find out how long a downtime I'll incur before committing to it in Prod.
    – MattyZDBA
    Commented Dec 29, 2016 at 19:07
2

Presumably you have rows dated after 2016-04-01? If so, they currently reside in the last partition.

Splitting the right-most range left function into two ranges will result in any rows dated later than 2016-04-01 being moved to the new range by virtue of the fact the ranges are defined as "range left".

You can see a nice visual of this at this MSDN page.

This MSDN page on ALTER PARTITION FUNCTION states:

Best Practices
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.

You'll want to make sure you add an extra partition after the current operation concludes to ensure you comply with above-stated best practice.

Improve this answer
2
  • 1
    Thank you Max. I initially was perplexed thinking that as long as my existing data was was dated before the boundary I don't have to incur data movement. I did a split on another partition function and scheme a little while ago with only a few seconds runtime but I discovered a significant difference: that function was created with RANGE RIGHT. I've fallen into the trap of RANGE LEFT functions. Lesson learned.
    – MattyZDBA
    Commented Dec 29, 2016 at 19:03
  • 1
    Partitioning is hard to do right. Or left, perhaps.
    – Hannah Vernon ♦
    Commented Dec 29, 2016 at 19:16

Your Answer

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

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