SQL Fascination

The first part of this topic provided a mini-guide to loading data into a partitioned table and a few helpful DMV based statements that can help you automate the process. The unloading of the data should in theory be easier, but to do this in an automated fashion you are more reliant on the DMVs and system views to get to the right information.

The steps to unload a partition of data are:

• Discover which file group the oldest partition is on.
• Create a staging table on the same filegroup with an identical schema and indexes
• Switch the data out to the staging table
• Merge the partition function
• Archive / Drop the data as appropriate.

 As in part 1, there are 3 sections of the process which are not so common, whilst the creation of a table and the archive / drop of the old data at the end is standard T-SQL that you will be using regularly.

Discover which Filegroup the Oldest Partition is On

When checking for the oldest filegroup, I have assumed that the basis of the rolling window is that the highest boundary is the most recent data, whilst the lowest boundary is the oldest – in essence time is moving forward and the partition key ascends, not descends. The oldest boundary will therefore be boundary 1, how do you get the Filegroup name of the filegroup this partition is on? A somewhat complex use of a set of DMV’s.

SELECT sys.filegroups.Name as FileGroupName FROM sys.partition_schemes 
INNER JOIN sys.destination_data_spaces ON sys.destination_data_spaces.partition_scheme_id = sys.partition_schemes.data_space_id
INNER JOIN sys.filegroups ON  sys.filegroups.data_space_id = sys.destination_data_spaces.data_space_ID
INNER JOIN sys.partition_range_values ON  sys.partition_range_values.Boundary_ID = sys.destination_data_spaces.destination_id
AND sys.partition_range_values.function_id = sys.partition_schemes.function_id
WHERE sys.partition_schemes.name = 'YourPartitionScheme'
and sys.partition_range_values.boundary_id = 1

This will return the name of file group, which allows you to create the staging table for the partition switch out on the correct filegroup.

Whilst the data space ID’s do alter in sequence depending on the partition function being a left or right based partition, the boundary ID remains consistent, which is why it is used to discover the oldest and not the destination_id / data_space_id.

Switch the Data Out to the Staging Table

Switching the data out is not complex, it just is the reverse syntax of switching the partition in essence. Under the hood you are redirecting IAM pointers, so the switch is considered a meta-data command and exceptionally fast.

ALTER TABLE YourPartitionedTable SWITCH PARTITION 1 TO YourStagingTable

The partition number used is in effect the boundary id, and the oldest boundary is for partition 1 the rolling window.

Merge the Partition Function

The last complex stage is the merging of the partition function, the command explicitly needs the value from the partition function that represents the partition. If you were doing this by hand you would know it, but to automate the process requires the discovery of this information from the DMV’s again.

SELECT value
FROM sys.partition_range_values
INNER JOIN sys.partition_functions ON sys.partition_functions.function_id = sys.partition_range_values.function_id  
 WHERE name = 'YourPartitionFunctionName' AND boundary_id = 1

Again, we are using the boundary value of 1 to extract only the oldest partition function value, but this can then be used in a partition function merge command.

ALTER PARTITION FUNCTION YourPartitionFunctionName() MERGE RANGE (YourBoundaryValue)

Conclusion

Using the DMV’s and appropriate stored procedures, the rolling window can be automated and does not require hand-crufted SQL to work – just use of the DMV’s to get the key values you need to be able to construct the harder parts of the process.

If you are following the guide on partition layout I wrote before, then the filegroup you have just removed the data from becomes the next spare filegroup to be used to house the next time data is imported. If you store this within the database, the next load will be able to automatically know where to place the data and set the next used filegroup value to, closing the loop so to speak.

I have covered how to layout a partitioned table across filegroups previously, but have not gone through the steps of rolling a partitioned window – it sounds a simple process but with all the file group and pre-requisites for it to run smoothly anyone starting with partitioned tables could probably use a little guide. As you are about to see the process is quite intricate so I will go through the load process on this post and the unload on the next.

Because no one case fits all, I have made some assumptions / limitations to provide a guide, specifically:

• The main partitioned table has a clustered index.
• The layout is following the mechanism of keeping a staging filegroup and spare filegroup as detailed in the layout post.
• The rollout process intends to remove the oldest data / partition.
• The process is designed for large loads, not single inserts.

So let’ s see what it takes to prepare and get data into a partitioned table:

• Create a staging table on your dedicated ETL filegroup, of an identical column schema to your partitioned table.
• Load the data into the staging table.
• Move the staging table to the spare filegroup, using a clustered index creation. (The need for the spare was covered in the layout post)
• Add any additional Non-Clustered indexes required to match the partitioned table indexes.
• Constrain the data so that it is considered trusted – the constraint must ensure all values are within the partition boundary you intend to place it within.
• Set the Partition Schema Next Used Filegroup
• Split the Partition Function
• Switch the staging table into the main partitioned table

That was all just to bulk load data into a partitioned table – a long list and plenty of opportunity for it to go wrong, but most of these steps use T-SQL that you will be very familiar with – it is only the last 3 items that use less common SQL and are harder to automate, since there is no built-in tools to do the work for you.

Setting the Next Used Filegroup

The intention when setting the filegroup is to declare where the partition should locate data for the partition function split that is about to occur. Whilst you can discover what the previous setting might be, it is not advisable to rely on it but set it every time, just before performing a partition function split. The syntax for the command is:

ALTER PARTITION SCHEME YourPartitionSchemeName NEXT USED [YourSpareFG]

Splitting the Partition Function

This splitting of the partition function is in effect the creation of an extra dividing section on the number line / date line representing the partitioned table. If you split a partition that already has data the operation will be quite expensive since can be forced to move data between filegroups, so it is common in a rolling window scenario that you split to handle the incoming data, which is always in advance of your existing data, e.g. If you are storing sales data partitioned by the month/year of the sales date, and currently only hold up until November, you would not insert any data for December until the partition for December had been created.

The syntax forward:

ALTER PARTITION FUNCTION YourPartitionFunctionName() SPLIT RANGE (YourBoundaryValue)

But when importing new data in an automated fashion, you might not know whether the new partition split has already been performed or not, so how can you check whether the new boundary value is already created in the partition function? DMV’s can provide the answer:

SELECT count(value) as ValueExists FROM sys.partition_range_values
INNER JOIN sys.PARTITION_FUNCTIONS ON  sys.PARTITION_FUNCTIONS.function_id  = sys.partition_range_values.function_id
WHERE name = 'YourPartitionFunctionName' AND value = YourBoundaryValue

A returned value of 0 would indicate it did not exist, whilst a 1 would indicate a boundary value had already been created.

Switching the Staging Table In

Switching the staging table into the newly created partition looks relatively easy but needs the partition number:

ALTER TABLE yourStagingTable SWITCH TO YourPartitionedTable PARTITION PartitionNumber

Where do you get the partition number from? The partition number is the boundary ID, and is numbered starting at 1 from the furthers left partition sequentially upwards. If you know the boundary value you have set for the partition, you can get the boundary id using the DMV’s again

SELECT boundary_id
FROM sys.partition_range_values
INNER JOIN sys.partition_functions ON sys.partition_functions.function_id  = sys.partition_range_values.function_id
WHERE  name = 'YourPartitionFunctionName' AND value= YourBoundaryValue

These additional DMVs allow you to get access to the data you need to automate the process in stored procedures, finding out the boundary IDs in one step, to be used in the next step etc.

These are the trickier parts of the process to automate that need the help of the DMVs. In the next post I will go through the unloading of the old data.