Modifying Partitions

In a previous post I talked about how to partition a table and touched on partition elimination which allows the optimiser to create a query plan where a much smaller amount of data is read.

A lot of the partitioning functions I’ve used in my working life are based on month, usually on reporting tables, where people need to be able to see all historic data but usually are only concerned with the current or last few months. When querying this data, partition elimination allows SQL Server to just read the data on the required partition(s) instead of the whole table. This is very useful if you have a lot of data going back a long time and usually query this data using a date type column.

However, when you first create your partition function based on a date you probably don’t go too far in the future and you might find that all of a sudden two years have passed and all your new data is just being put on a single large latest partition that just keeps growing as the months go by.

Even worse than that you might find the situation I had today where a new database had been recently released to production using an old database schema as a rough template. This database only contains data from March 2018 onward but the partition function for a certain large table was split by month from 1st Jan 2012 to 1st Jan 2016. So there were 48 partitions covering the 4 years from 2012 to 2015 inclusive but all the data is just sitting in the last partition.

What I needed to do was get rid of all the old partitions and just have a single one up to March 2018 and then create new partitions every month for the next few months.

I tried changing the partition function in SSDT and letting that sort things out but it attempts to rebuild the table and drop and recreate everything.

There is a way to do this using the ALTER PARTITION FUNCTION and ALTER PARTITION SCHEME statements and this is how I did it…

To recreate this issue I’ve created a new partition function named PF_MonthlyFinance which has monthly partitions for 2012 to 2015. Then I created a new partition scheme named PS_MonthlyFinance for this function with all partitions on the PRIMARY filegroup. This is the first few rows of the sys.partition_range_values view…

01

… and this is the last few rows…

02

First I thought I’d create the new partitions. We can do this using the split method of the ALTER PARTITION FUNCTION to add the first partition for March 2018.

ALTER PARTITION FUNCTION PF_MonthlyFinance () 
SPLIT RANGE ('20180301');

Now if we look at the last few rows of sys.partition_range_values we can see the new partition…

03

So now let’s add the new one for April 2018…

ALTER PARTITION FUNCTION PF_MonthlyFinance () 
SPLIT RANGE ('20180401');

However, when we run this we get the following error…

Msg 7710, Level 16, State 1, Line 16
Warning: The partition scheme 'PS_MonthlyFinance' does not have any next used filegroup. Partition scheme has not been changed.

You might have noticed that when we created the PARTITION SCHEME the following message was returned…

Partition scheme 'PS_MonthlyFinance' has been created successfully. 'PRIMARY' is marked as the next used filegroup in partition scheme 'PS_MonthlyFinance'.

The next filegroup has already been used when creating the partition for March 2018 so now we need to say what filegroup the next partition should be on. We can do this with the ALTER PARTITION SCHEME statement as follows…

ALTER PARTITION SCHEME PS_MonthlyFinance 
NEXT USED [PRIMARY];

We can re-run the following and now it works fine…

ALTER PARTITION FUNCTION PF_MonthlyFinance () 
SPLIT RANGE ('20180401');

04

Rather than adding each partition manually we can script it out to create a few in one go. I’ve added this code at the bottom of this post.

Before that I want to show how we can get rid of the old redundant partitions. Again we can use the ALTER PARTITION FUNCTION but this time we use the merge method instead of split as follows…

ALTER PARTITION FUNCTION PF_MonthlyFinance () 
MERGE RANGE ('20120101');

Now if we look at the first few rows of sys.partition_range_values we can see the partition for Jan 2012 is now gone…

05

Here’s a single piece of code that can be used to delete all these old partitions…

DECLARE @StartDate DATE = '20120101';
DECLARE @EndDate DATE = '20160101';

WHILE @StartDate < @EndDate
BEGIN

  IF EXISTS (
    SELECT 1
    FROM sys.partition_functions PF
    JOIN sys.partition_range_values PRV ON PRV.function_id = PF.function_id
    WHERE PF.name = 'PF_MonthlyFinance'
    AND PRV.value = @StartDate
  )
  BEGIN

    ALTER PARTITION FUNCTION PF_MonthlyFinance () 
    MERGE RANGE (@StartDate);

  END
 
  SELECT @StartDate = DATEADD(MONTH, 1, @StartDate);

END

We can now see only the two new partitions exist…

06

Now we can use the following to create the next batch of partitions going forward…

DECLARE @StartDate DATE = '20180301';
DECLARE @NumParts INT = 24;
DECLARE @Counter INT = 1;

WHILE @Counter <= @NumParts
BEGIN

  IF NOT EXISTS (
    SELECT 1
    FROM sys.partition_functions PF
    JOIN sys.partition_range_values PRV ON PRV.function_id = PF.function_id
    WHERE PF.name = 'PF_MonthlyFinance'
    AND PRV.value = @StartDate
  )
  BEGIN

    ALTER PARTITION FUNCTION PF_MonthlyFinance () 
    SPLIT RANGE (@StartDate);

    ALTER PARTITION SCHEME PS_MonthlyFinance 
    NEXT USED [PRIMARY];

  END
 
  SELECT @StartDate = DATEADD(MONTH, 1, @StartDate);
  SELECT @Counter += 1;

END

This will create 24 monthly partitions starting from March 2018 and now we can see all the new partitions exist…

07

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s