How to implement table purge with partition on SQL Server 2012?

Question

I've read this question: How to remove a table partition

But it's not what I want.

I'd like to setup a table, using partitioning on a date/time column to have 1 month partitions. Afterwards, configure a job that executes montlhy and drops the partition(s) that have records over 9 months old.

Can I set this up using SQL Server 2012 Enterprise?

Answer 1

The goal is to reduce the amount of transaction log by only updating metadata.

When data is move or remove from a partition, it Insert and Delete rows which will result in (tons of) LOB_INSERT_ROWS and LOB_DELETE_ROW in the transaction logs.

The only option would be to truncate the partition but this option does not exist. We can avoid it by only using Merge and Split on empty Partitions.

In the sample below, I will keep things shorter and only create data for the past 3 months (ie. August, September and October) but you can easily expand it to 9 months. August will be removed once data starts being added to November and so on with September and December...

Create Files and Filegroups:

I first create 6 files and file groups [Part_0] to [Part_5]:

Alter Database [Test] Add Filegroup [Part_0];
...
Alter Database [Test] Add Filegroup [Part_5];
Alter Database [Test] Add File( NAME = N'Part_0', FILENAME = N'...\Part_0.ndf' , SIZE = 100MB , FILEGROWTH = 100MB ) TO Filegroup [Part_0];
...
Alter Database [Test] Add File( NAME = N'Part_5', FILENAME = N'...\Part_5.ndf' , SIZE = 100MB , FILEGROWTH = 100MB ) TO Filegroup [Part_5];

Create Function and Scheme:

Create Partition Function [DateKeyPartFunction] (datetime2)
as Range Right For Values ('20150801', '20150901', '20151001', '20151101', '20151201');
Create Partition Scheme [DateKeyPartScheme] as Partition [DateKeyPartFunction]
To ([Part_0], [Part_1], [Part_2], [Part_3], [Part_4], [Part_5]);

Again there are 6 partitions. This will be explained later but this is mostly due to the need to have empty partitions.

Create Table and Clustered Index:

Because I don't know the exact design of your table, I will be working with this table:

Create Table dbo.DataPart(id int identity(0, 1), name char(1000), name_date datetime2);

and this Clustered Index:

Create Clustered Index IDX_Part On dbo.DataPart(name_date) On DateKeyPartScheme(name_date);

Dummy data:

This code creates over a million record in a range of dummy dates every 6 seconds from October to August(now):

With inc(n) as(
    Select ROW_NUMBER() over(order by (select 1))-1 From (
        Select 1 From (values(1), (1), (1), (1), (1), (1), (1), (1), (1), (1)) as x1(n)
        Cross Join (values(1), (1), (1), (1), (1), (1), (1), (1), (1), (1)) as x2(n)
        Cross Join (values(1), (1), (1), (1), (1), (1), (1), (1), (1), (1)) as x3(n)
        Cross Join (values(1), (1), (1), (1), (1), (1), (1), (1), (1), (1)) as x4(n)
        Cross Join (values(1), (1), (1), (1), (1), (1), (1), (1), (1), (1)) as x5(n)
        Cross Join (values(1), (1), (1), (1), (1), (1), (1), (1), (1), (1)) as x6(n)
    ) as x(n)
)
Insert into dbo.DataPart(name, name_date) 
Select TOP(1000000) '', DATEADD(second, -n*6, getdate()) From inc;

Partitioned Data:

This will partionned as follow:

Id Partition    Left Bound      Right Bound     Row Count
1  [Part_0]                 < '20150801'    0
2  [Part_1] >= '20150801'   < '20150901'    184042
3  [Part_2] >= '20150901'   < '20151001'    432000
4  [Part_3] >= '20151001'   < '20151101'    383958
5  [Part_4] >= '20151101'   < '20151201'    0
6  [Part_5] >= '20151201'                   0

• [Part_1], [Part_2], [Part_3] contain data for August, September and October.
• [Part_0] (ie. <'20150801') only exists because it needs an empty partition before we can empty [Part_1] (August) once in November. It would be data for July and before but must remain empty.
• It make things easier to already have an empty partition for next month although it will remain empty until it reach November. This is the purpose of [Part_4] for November.
• Partition Scheme needs an extra partition for everything outside of the right boundary. This is [Part_5] for December and beyond and it must remain empty as well.

Add November:

Once in November, new rows will go to [Part_4] and August data can be removed from [Part_1]. The only way to remove it without having to delete hundred of thousands of rows is to move [Part_1] away from the table:

Create Table dbo.DataPart_Temp(id int identity(0, 1), name char(1000), name_date datetime2);
Create Clustered Index IDX_Part_temp On dbo.DataPart_temp(name_date) On [Part_1];
Alter Table DataPart Switch Partition 2 to DataPart_temp Partition 1;

• DataPart_Temp must be identical to DataPart (columns, indexes)
• Because [Part_1] is move from DataPart to DataPart_temp, the clustered index on DataPart_temp must be created on the same filegroup: [Part_1]
• [Part_1] is the second partition of DataPart and is switch to the first and only Partition of DataPart_temp. All August rows are now in DataPart_temp.

the table is now partitioned as follow:

id Partition    Left Bound      Right Bound     Row Count
1  [Part_0]                 < '20150801'    0
2  [Part_1] >= '20150801'   < '20150901'    0
3  [Part_2] >= '20150901'   < '20151001'    432000
4  [Part_3] >= '20151001'   < '20151101'    383958
5  [Part_4] >= '20151101'   < '20151201'    0
6  [Part_5] >= '20151201'                   0

Merge Partition:

[Part_0] and [Part_1] are now empty and can be merge:

Alter Partition Function [DateKeyPartFunction]() Merge Range ('20150801');

[Part_1] has been removed:

id Partition    Left Bound      Right Bound     Row Count
1  [Part_0]                 < '20150901'    0
2  [Part_2] >= '20150901'   < '20151001'    432000
3  [Part_3] >= '20151001'   < '20151101'    383958
4  [Part_4] >= '20151101'   < '20151201'    0
5  [Part_5] >= '20151201'                   0

Add Next month:

Now that [Part_1] is not used anymore, it can be added to the Partition Scheme as the next available partition:

Alter Partition Scheme [DateKeyPartScheme] Next Used [Part_1];

And then [Part_5] (December, >= '20151201') can be splited:

Alter Partition Function [DateKeyPartFunction]() Split Range ('20160101');

Since [Part_5] is empty, nothing has to be move. The other half of [Part_5] will go to the next available partition which is [Part_1]:

Id Partition    Left Bound      Right Bound     Row Count
1  [Part_0]                 < '20150901'    0
2  [Part_2] >= '20150901'   < '20151001'    432000
3  [Part_3] >= '20151001'   < '20151101'    383958
4  [Part_4] >= '20151101'   < '20151201'    x rows in November
5  [Part_5] >= '20151201'   < '20160101'    0
6  [Part_1] >= '20160101'                   0

Clean Up:

DataPart_temp can now be truncated and then dropped (or at least drop its Clustered Index).

Add Following months:

In December, Partition ID 2 must be move away ([Part_2]), merged with partition 1 and then added back before January split.

In order to automate this process, you will have to look for the name of the filegroup for Partition 2:

And then create dynamic SQL to:

• Add Clustered Index to DataPart_temp on [Part_X] (similar to DataPart)
• Switch [Part_X] from DataPart to DataPart_temp
• Merge Partition 1 and 2
• Add [Part_X] to DataPart
• Split the last partition
• Truncate DataPart_temp
• Drop DataPart_temp or its Clustered Index

If you use fn_dblog, you should see that transaction logs are minimal.