Manage Multiple Partitions in Multiple Filegroups in SQL Server

Problem

SQL Server Books Online (BOL) suggests that in order to clean up old partitions from a partition table, you should either issue a DELETE or SWITCH command. BOL also suggests that while SWITCHING partitions, you should set up a NON PARTITIONED TABLE in the same filegroup where the original partition is located. The BOL solution works if you need to manage a couple of partitions since creating a couple of tables in each filegroup is not that time consuming. However, this problem becomes very challenging when you need to manage many historical partitions located on many different filegroups.

Solution

This solution talks about how to manage old partitions without creating a NON PARTITIONED table in each filegroup. If you are not dealing with multiple filegroups, you can refer to this other tip Switching data in and out of a SQL Server 2005 data partition since that tip talks very clearly about how to manage partitions if you have only one filegroup such as the PRIMARY filegroup..

Considering, in a real heavy loaded environment, you might have multiple filegroups within a partition scheme, it is not feasible to create NON partitioned table on every filegroup for the cleanup purpose. This tip shows how you can switch multiple partitions by creating another partitioned table using the same Partition Scheme as the original table.

Step 1
To start with, let’s create a sample database which contains several filegroups for our demo purposes.


 <span style="color: green;">--Create the Database
 </span>
 <span style="color: blue;">CREATE DATABASE </span>
 <span style="color: black;">MSSQLTIPS</span><span style="color: green;">--Now Create Five Filegroups

ALTER DATABASE MSSQLTIPS ADD filegroup FGDayRange01 ALTER DATABASE MSSQLTIPS ADD filegroup FGDayRange02 ALTER DATABASE MSSQLTIPS ADD filegroup FGDayRange03 ALTER DATABASE MSSQLTIPS ADD filegroup FGDayRange04 ALTER DATABASE MSSQLTIPS ADD filegroup FGDayRange05

Step 2
Now let’s create the files for each filegroup.


 <span style="color: green;">--Now Add One File to Each Filegroup
 </span><span style="color: blue;">ALTER DATABASE </span>
 <span style="color: black;">MSSQLTIPS
 </span>
 <span style="color: blue;">ADD FILE
 </span>
 <span style="color: gray;">(
 </span><span style="color: black;">NAME
 </span><span style="color: blue;">= </span>
 <span style="color: black;">FGDayRange_01</span><span style="color: gray;">,
 </span><span style="color: black;">FILENAME </span>
 <span style="color: blue;">= </span><span style="color: red;">'C:\FGDayRange_01.NDF'</span><span style="color: gray;">,
 </span><span style="color: black;">SIZE </span>
 <span style="color: blue;">= </span><span style="color: black;">5MB</span><span style="color: gray;">,
 </span><span style="color: black;">FILEGROWTH </span>
 <span style="color: blue;">= </span><span style="color: black;">1MB
</span><span style="color: gray;">)
 </span>
 <span style="color: blue;">TO </span><span style="color: black;">FILEGROUP
 FGDayRange01</span><span style="color: gray;">
 <span style="color: blue;">ALTER DATABASE </span>
 <span style="color: black;">MSSQLTIPS
 </span>
 <span style="color: blue;">ADD FILE
 </span>
 <span style="color: gray;">(
 </span><span style="color: black;">NAME
 </span><span style="color: blue;">= </span>
 <span style="color: black;">FGDayRange_02</span><span style="color: gray;">,
 </span><span style="color: black;">FILENAME </span>
 <span style="color: blue;">= </span><span style="color: red;">'C:\FGDayRange_02.NDF'</span><span style="color: gray;">,
 </span><span style="color: black;">SIZE </span>
 <span style="color: blue;">= </span><span style="color: black;">5MB</span><span style="color: gray;">,
 </span><span style="color: black;">FILEGROWTH </span>
 <span style="color: blue;">= </span><span style="color: black;">1MB
</span><span style="color: gray;">)
 </span>
 <span style="color: blue;">TO </span><span style="color: black;">FILEGROUP
 FGDayRange02</span><span style="color: gray;">
 <span style="color: blue;">ALTER DATABASE </span>
 <span style="color: black;">MSSQLTIPS
 </span>
 <span style="color: blue;">ADD FILE
 </span>
 <span style="color: gray;">(
 </span><span style="color: black;">NAME
 </span><span style="color: blue;">= </span>
 <span style="color: black;">FGDayRange_03</span><span style="color: gray;">,
 </span><span style="color: black;">FILENAME </span>
 <span style="color: blue;">= </span><span style="color: red;">'C:\FGDayRange_03.NDF'</span><span style="color: gray;">,
 </span><span style="color: black;">SIZE </span>
 <span style="color: blue;">= </span><span style="color: black;">5MB</span><span style="color: gray;">,
 </span><span style="color: black;">FILEGROWTH </span>
 <span style="color: blue;">= </span><span style="color: black;">1MB
</span><span style="color: gray;">)
 </span>
 <span style="color: blue;">TO </span><span style="color: black;">FILEGROUP
 FGDayRange03</span><span style="color: gray;">
 <span style="color: blue;">ALTER DATABASE </span>
 <span style="color: black;">MSSQLTIPS
 </span>
 <span style="color: blue;">ADD FILE
 </span>
 <span style="color: gray;">(
 </span><span style="color: black;">NAME
 </span><span style="color: blue;">= </span>
 <span style="color: black;">FGDayRange_04</span><span style="color: gray;">,
 </span><span style="color: black;">FILENAME </span>
 <span style="color: blue;">= </span><span style="color: red;">'C:\FGDayRange_04.NDF'</span><span style="color: gray;">,
 </span><span style="color: black;">SIZE </span>
 <span style="color: blue;">= </span><span style="color: black;">5MB</span><span style="color: gray;">,
 </span><span style="color: black;">FILEGROWTH </span>
 <span style="color: blue;">= </span><span style="color: black;">1MB
</span><span style="color: gray;">)
 </span>
 <span style="color: blue;">TO </span><span style="color: black;">FILEGROUP
 FGDayRange04</span><span style="color: gray;">
 <span style="color: blue;">ALTER DATABASE </span>
 <span style="color: black;">MSSQLTIPS
 </span>
 <span style="color: blue;">ADD FILE
 </span>
 <span style="color: gray;">(
 </span><span style="color: black;">NAME
 </span><span style="color: blue;">= </span>
 <span style="color: black;">FGDayRange_05</span><span style="color: gray;">,
 </span><span style="color: black;">FILENAME </span>
 <span style="color: blue;">= </span><span style="color: red;">'C:\FGDayRange_05.NDF'</span><span style="color: gray;">,
 </span><span style="color: black;">SIZE </span>
 <span style="color: blue;">= </span><span style="color: black;">5MB</span><span style="color: gray;">,
 </span><span style="color: black;">FILEGROWTH </span>
 <span style="color: blue;">= </span><span style="color: black;">1MB
</span><span style="color: gray;">)
 </span>
 <span style="color: blue;">TO </span><span style="color: black;">FILEGROUP
 FGDayRange05</span><span style="color: gray;">
 <span style="color: blue;">USE </span><span style="color: black;">mssqltips

GO 

Step 3
We then create our partition function.

–Now Create a Partition Function
CREATE
Partition
FUNCTION
pfFiveDayRange
(Datetime)

AS
RANGE
RIGHT
FOR VALUES
(‘2008-09-01’,
‘2008-09-02’,
‘2008-09-03’,
‘2008-09-04’,
‘2008-09-05’
)
GO

Step 4
Then we create our partition scheme.


 <span style="color: green;">--Now create a Partition Scheme to Hold Five
 Filegroups
 --These Five Filegroups will hold the data for the five
 dates defined in Partition Function.
 </span>
 <span style="color: blue;">CREATE </span><span style="color: black;">PARTITION
 SCHEME psFiveDayRange
 </span><span style="color: blue;">AS </span>
 <span style="color: black;">PARTITION pfFiveDayRange
 </span>
 <span style="color: blue;">TO </span><span style="color: gray;">(
 </span><span style="color: black;">[PRIMARY]</span><span style="color: gray;">,
 </span><span style="color: black;">[FGDayRange01]</span><span style="color: gray;">,
 </span><span style="color: black;">[FGDayRange02]</span><span style="color: gray;">,
 </span><span style="color: black;">[FGDayRange03]</span><span style="color: gray;">,
 </span><span style="color: black;">[FGDayRange04]</span><span style="color: gray;">,
 </span><span style="color: black;">[FGDayRange05] </span>

Step 5
We create a new partitioned table.


 <span style="color: green;">--Now create a Partitioned Table.
 </span><span style="color: blue;">CREATE TABLE </span>
 <span style="color: black;">MSSQLTIPS_Partitions
 </span>
 <span style="color: gray;">(</span><span style="color: black;">ID
 </span><span style="color: blue;">INT </span>
 <span style="color: #434343;">IDENTITY</span><span style="color: gray;">,

businessDate DATETIME NOT NULL) ON psFiveDayRange (businessDate

Step 6
We load some sample data.


 <span style="color: blue;">SET </span><span style="color: black;">NOCOUNT
 </span><span style="color: blue;">ON
 </span>
 <span style="color: green;">--Load 50 records with each date
 </span><span style="color: blue;">INSERT INTO </span>
 <span style="color: black;">MSSQLTIPS_Partitions </span>
 <span style="color: gray;">(</span><span style="color: black;">businessDate</span><span style="color: gray;">)
</span><span style="color: blue;">VALUES </span>
 <span style="color: gray;">(</span><span style="color: red;">'2008-09-01'</span><span style="color: gray;">)
</span><span style="color: black;">GO 50
 </span>
 <span style="color: blue;">INSERT INTO </span>
 <span style="color: black;">MSSQLTIPS_Partitions </span>
 <span style="color: gray;">(</span><span style="color: black;">businessDate</span><span style="color: gray;">)
</span><span style="color: blue;">VALUES </span>
 <span style="color: gray;">(</span><span style="color: red;">'2008-09-02'</span><span style="color: gray;">)
</span><span style="color: black;">GO 50
 </span>
 <span style="color: blue;">INSERT INTO </span>
 <span style="color: black;">MSSQLTIPS_Partitions </span>
 <span style="color: gray;">(</span><span style="color: black;">businessDate</span><span style="color: gray;">)
</span><span style="color: blue;">VALUES </span>
 <span style="color: gray;">(</span><span style="color: red;">'2008-09-03'</span><span style="color: gray;">)
</span><span style="color: black;">GO 50
 </span>
 <span style="color: blue;">INSERT INTO </span>
 <span style="color: black;">MSSQLTIPS_Partitions </span>
 <span style="color: gray;">(</span><span style="color: black;">businessDate</span><span style="color: gray;">)
</span><span style="color: blue;">VALUES </span>
 <span style="color: gray;">(</span><span style="color: red;">'2008-09-04'</span><span style="color: gray;">)
</span><span style="color: black;">GO 50
 </span>
 <span style="color: blue;">INSERT INTO </span>
 <span style="color: black;">MSSQLTIPS_Partitions </span>
 <span style="color: gray;">(</span><span style="color: black;">businessDate</span><span style="color: gray;">)
</span><span style="color: blue;">VALUES </span>
 <span style="color: gray;">(</span><span style="color: red;">'2008-09-05'</span><span style="color: gray;">)

GO 50 

Step 7
Here we check to see how the data was loaded and how many rows are in each partition.


 <span style="color: green;">-- By Now you have 250 records (50 Each Date)
 sitting on each Filegroup.
 -- Let's check this out.
 </span>
 <span style="color: blue;">SELECT </span>
 <span style="color: magenta;">COUNT</span><span style="color: gray;">(*)
 </span><span style="color: blue;">AS </span>
 <span style="color: black;">[Total Records]</span><span style="color: gray;">,
 </span><span style="color: black;">$Partition.pfFiveDayRange</span><span style="color: gray;">(</span><span style="color: black;">businessDate</span><span style="color: gray;">)
 </span><span style="color: blue;">AS </span>
 <span style="color: black;">[Partition Number]
 </span>
 <span style="color: blue;">FROM </span><span style="color: black;">MSSQLTIPS_Partitions

GROUP BY $Partition.pfFiveDayRange(businessDate

Step 8
Now you want to clean up the partitions for dates 2008-09-01, 2008-09-02, and 2008-09-03. The traditional way to do so is by creating three NON PARTITIONED tables in each filegroup FGDayRange01, FGDayRange02, FGDayRange03 and switching each partition to the corresponding NON PARTITIONED table. Now, you can forget about doing all that and do it all in one step by creating another PARTITIONED TABLE using the same partition scheme as the original table.


 <span style="color: green;">--Note that the DDL for the cleanup table
 must be exactly same as the original table.
 </span>
 <span style="color: blue;">CREATE TABLE </span>
 <span style="color: black;">MSSQLTIPS_Partitions_Cleanup
 </span>
 <span style="color: gray;">(</span><span style="color: black;">ID
 </span><span style="color: blue;">INT </span>
 <span style="color: #434343;">IDENTITY</span><span style="color: gray;">,

Step 9
Now you can switch all partitions within one table since the partition scheme takes care of the physical layout of the structure.

<code style="font-size: 12px;">
 <span style="color: blue;">ALTER TABLE </span>
 <span style="color: black;">MSSQLTIPS_Partitions
 SWITCH PARTITION
 $PARTITION.pfFiveDayRange</span><span style="color: gray;">(</span><span style="color: red;">'2008-09-01'</span><span style="color: gray;">)

TO MSSQLTIPS_Partitions_Cleanup PARTITION $PARTITION.pfFiveDayRange('2008-09-01')

<code style="font-size: 12px;">ALTER TABLE MSSQLTIPS_Partitions SWITCH PARTITION $PARTITION.pfFiveDayRange('2008-09-02')

ALTER TABLE MSSQLTIPS_Partitions SWITCH PARTITION $PARTITION.pfFiveDayRange('2008-09-03')

Step 10
Run a count to see that data has been switched to the clean up table.

<code style="font-size: 12px;">
 <span style="color: blue;">SELECT </span>
 <span style="color: magenta;">COUNT</span><span style="color: gray;">(*)
 </span><span style="color: blue;">FROM </span>
 <span style="color: black;">MSSQLTIPS_Partitions </span>
 <span style="color: green;">-- Should return 100 Records
 </span>
 <span style="color: blue;">SELECT </span>
 <span style="color: magenta;">COUNT</span><span style="color: gray;">(*)
 </span><span style="color: blue;">FROM </span>
 <span style="color: black;">MSSQLTIPS_Partitions_Cleanup </span>
 <span style="color: green;">-- Should Return 150 Records</span>

— See
Partition Details

SELECT COUNT(*) AS [Total Records], $Partition.pfFiveDayRange(businessDate) AS [Partition Number] FROM MSSQLTIPS_Partitions

GROUP BY $Partition.pfFiveDayRange(businessDateSELECT COUNT(*) AS [Total Records], $Partition.pfFiveDayRange(businessDate) AS [Partition Number] FROM MSSQLTIPS_Partitions_Cleanup

GROUP BY $Partition.pfFiveDayRange(businessDate

Step 11
Now the three dates from the original table have been switched to the cleanup table. Now you can simply truncate the cleanup table.


 <span style="color: blue;">TRUNCATE TABLE </span>
 <span style="color: black;">MSSQLTIPS_Partitions_Cleanup </span>

Now you are all done. You can merge partitions in the original table or reload the table with the correct data or do anything else you wish. I hope you all enjoyed the idea of not creating multiple non-partitioned tables to hold the partitions for switch command.

Next Steps

You can go to SQL Server BOL and read more on the following topics
- $Partition Function
- CREATE PARTITION SCHEME
- CREATE PARTITION FUNCTION
- FILEGROUP
- Sys.Partition% system table/views
- Sys.destination_data_spaces

Nitin Rana

Nitin is a Microsoft Certified Professional and currently has over 12 years of IT experience under his umbrella. He is currently working as SQL Architect at Wachovia Securities in New York City. Nitin has an extensive knowledge in maintaining VLDB (more than 3-4 TB) and is very proficient in SQL server core components such as Indexing, Performance Tuning, Partitioning and so on. He has worked at various pristine clients in the continental United States helping the clients to reach a stable stage in their database platform. Nitin also has extensive experience in ASP, Power Builder, SSAS, SSRS to name a few.

Leave a ReplyCancel Reply