http://www.sqlservercentral.com/blogs/technobabble-by-klee-from-kleegeek/2013/01/14/example-of-sql-server-table-partitioning/

Printed 2014/08/27 09:39AM

Example of SQL Server table partitioning

By kleegeek, 2013/01/14

Recently, one of my favorite customers asked me some very thought-provoking questions about using database partitioning to move transactional data around. Their current process undergoes a pretty large delete operation nightly, and they want to improve the speed and efficiency of the processes. So, being a tad rusty on partitioning, I whipped up a demo script that uses table-level partitioning to divide up inbound data based on a monthly date range and move the data to a new table for the delete processing. The new table can be truncated instantly, rather than the current process of batch deleting, which is less efficient. 

OK – let’s create a junk 2012 database to tinker with.

CREATE DATABASE [PartitionTest1] CONTAINMENT = NONE ON PRIMARY ( NAME = N'PartitionTest', FILENAME = N'E:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\PartitionTest.mdf' , SIZE = 4096KB , FILEGROWTH = 1024KB ) LOG ON ( NAME = N'PartitionTest_log', FILENAME = N'E:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\PartitionTest_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%) GO

For SQL Server 2008R2 and below, remove the ‘Containment = NONE’ bit of code.

Now, we’ll add file groups to ensure data file placement flexibility. I’m adding one per month of data.

--add file groups for future data placement flexibility ALTER DATABASE PartitionTest1 ADD FILEGROUP FGMonth01 ALTER DATABASE PartitionTest1 ADD FILEGROUP FGMonth02 ALTER DATABASE PartitionTest1 ADD FILEGROUP FGMonth03 ALTER DATABASE PartitionTest1 ADD FILEGROUP FGMonth04 ALTER DATABASE PartitionTest1 ADD FILEGROUP FGMonth05 ALTER DATABASE PartitionTest1 ADD FILEGROUP FGMonth06 ALTER DATABASE PartitionTest1 ADD FILEGROUP FGMonth07 ALTER DATABASE PartitionTest1 ADD FILEGROUP FGMonth08 ALTER DATABASE PartitionTest1 ADD FILEGROUP FGMonth09 ALTER DATABASE PartitionTest1 ADD FILEGROUP FGMonth10 ALTER DATABASE PartitionTest1 ADD FILEGROUP FGMonth11 ALTER DATABASE PartitionTest1 ADD FILEGROUP FGMonth12 go

Next, add data files corresponding to the new file groups. The files are on my laptop, so they all go to the same place, but they can be placed almost anywhere.

--add new database data files corresponding to the new file groups ALTER DATABASE PartitionTest1 ADD FILE (NAME = N'Month01', FILENAME = N'E:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\PartitionTestMonth01.ndf', SIZE = 5MB, FILEGROWTH = 5MB) TO FILEGROUP [FGMonth01] go ALTER DATABASE PartitionTest1 ADD FILE (NAME = N'Month02', FILENAME = N'E:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\PartitionTestMonth02.ndf', SIZE = 5MB, FILEGROWTH = 5MB) TO FILEGROUP [FGMonth02] go ALTER DATABASE PartitionTest1 ADD FILE (NAME = N'Month03', FILENAME = N'E:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\PartitionTestMonth03.ndf', SIZE = 5MB, FILEGROWTH = 5MB) TO FILEGROUP [FGMonth03] go ALTER DATABASE PartitionTest1 ADD FILE (NAME = N'Month04', FILENAME = N'E:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\PartitionTestMonth04.ndf', SIZE = 5MB, FILEGROWTH = 5MB) TO FILEGROUP [FGMonth04] go ALTER DATABASE PartitionTest1 ADD FILE (NAME = N'Month05', FILENAME = N'E:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\PartitionTestMonth05.ndf', SIZE = 5MB, FILEGROWTH = 5MB) TO FILEGROUP [FGMonth05] go ALTER DATABASE PartitionTest1 ADD FILE (NAME = N'Month06', FILENAME = N'E:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\PartitionTestMonth06.ndf', SIZE = 5MB, FILEGROWTH = 5MB) TO FILEGROUP [FGMonth06] go ALTER DATABASE PartitionTest1 ADD FILE (NAME = N'Month07', FILENAME = N'E:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\PartitionTestMonth07.ndf', SIZE = 5MB, FILEGROWTH = 5MB) TO FILEGROUP [FGMonth07] go ALTER DATABASE PartitionTest1 ADD FILE (NAME = N'Month08', FILENAME = N'E:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\PartitionTestMonth08.ndf', SIZE = 5MB, FILEGROWTH = 5MB) TO FILEGROUP [FGMonth08] go ALTER DATABASE PartitionTest1 ADD FILE (NAME = N'Month09', FILENAME = N'E:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\PartitionTestMonth09.ndf', SIZE = 5MB, FILEGROWTH = 5MB) TO FILEGROUP [FGMonth09] go ALTER DATABASE PartitionTest1 ADD FILE (NAME = N'Month10', FILENAME = N'E:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\PartitionTestMonth10.ndf', SIZE = 5MB, FILEGROWTH = 5MB) TO FILEGROUP [FGMonth10] go ALTER DATABASE PartitionTest1 ADD FILE (NAME = N'Month11', FILENAME = N'E:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\PartitionTestMonth11.ndf', SIZE = 5MB, FILEGROWTH = 5MB) TO FILEGROUP [FGMonth11] go ALTER DATABASE PartitionTest1 ADD FILE (NAME = N'Month12', FILENAME = N'E:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\PartitionTestMonth12.ndf', SIZE = 5MB, FILEGROWTH = 5MB) TO FILEGROUP [FGMonth12] go

Next, we’ll use the new database and start on the partitioning details. A function is created that will return the integer corresponding to the month of the data’s date. A partition function is created that gives us our possible value map. A partition scheme is built so that these values are mapped to the partitions and corresponding file groups, based on the function’s return value.

use PartitionTest1 go --create function for getting correct partition number from month of date CREATE FUNCTION [dbo].[fnGetPartition](@Dt Date) RETURNS TINYINT WITH SCHEMABINDING AS BEGIN DECLARE @RetVal TINYINT SET @RetVal = datepart(mm, @dt) RETURN @RetVal END GO --create possible values for the partitioning function --notice that 12 will be bundled with the right side of the upcoming partitioning scheme CREATE PARTITION FUNCTION [pfPartition](TINYINT) AS RANGE LEFT FOR VALUES ( 1,2,3,4,5,6,7,8,9,10,11 ) GO --create the partition scheme that lines up with the new file groups CREATE PARTITION SCHEME psPartitionTable AS PARTITION pfPartition TO (FGMonth01,FGMonth02,FGMonth03,FGMonth04,FGMonth05,FGMonth06,FGMonth07,FGMonth08,FGMonth09,FGMonth10,FGMonth11,FGMonth12) GO

Now, we’ll create two tables. The first is for the yearly storage of the transactional data. The second table is the staging table where the monthly data would be moved into, processed, and then deleted. For the partitioning transfer to work, these tables should be nearly identical. The tables must have the same column structure and order. The destination table does not need to be explicitly partitioned, however. I like to keep things the same so that you do not see a warning informing you of this difference.

--create the new test table CREATE TABLE [dbo].[TestTable1]( [PartitionNum] AS dbo.fnGetPartition(GeneratedDate) PERSISTED, [GeneratedDate] [date] NOT NULL ) ON psPartitionTable(PartitionNum) GO --create the staging table to be used for the data deletes. Note: it must be the same --structure as the source table. CREATE TABLE [dbo].[StagingTable1]( [PartitionNum] AS dbo.fnGetPartition(GeneratedDate) PERSISTED, [GeneratedDate] [date] NOT NULL ) ON psPartitionTable(PartitionNum) go

Now, we’ll insert some randomly generated date values into this table. Thanks to Latif Khan for a cool script to generate random dates.

--insert 5000 randomly generated dates. --thanks http://www.latifkhan.co.uk/index.php/select-random-date-time-in-sql-server/ for the cool query! insert into dbo.TestTable1 (GeneratedDate) select cast( cast( getdate() as int ) -5555 * rand( cast( cast( newid() as binary(8) ) as int ) ) as datetime ) go 5000 --do this 5000 times.

Let’s see just what this looks like. The following query will give us all of the partitions inside the table, and the row counts per partition.

SELECT     p.partition_number, fg.name, p.rows FROM     sys.partitions p INNER JOIN sys.allocation_units au ON au.container_id = p.hobt_id INNER JOIN sys.filegroups fg ON fg.data_space_id = au.data_space_id WHERE     p.object_id = OBJECT_ID('dbo.TestTable1') go

Results:

partition_01

In the following bits of code,  the partition that I wish to relocate corresponds to the month of April (month number 4). We’ll switch the partition to the new StagingTable1′s partition number four. Ensure that this partition is empty or else this command will fail. Only metadata is updated to make this switch, so it’s very quick and very efficient.

--random month = april (month number 4) alter table dbo.TestTable1 switch partition 4 to dbo.[StagingTable1] partition 4 go

Sweet. Rerun the check query to look at row counts.

SELECT p.partition_number, fg.name, p.rows FROM sys.partitions p INNER JOIN sys.allocation_units au ON au.container_id = p.hobt_id INNER JOIN sys.filegroups fg ON fg.data_space_id = au.data_space_id WHERE p.object_id = OBJECT_ID('dbo.TestTable1') go

Results:

partition_02

And, now the staging table contains our data!

select * from dbo.stagingtable1

partition_03

Finally, our transactional data can be deleted with a table truncate, which is very quick instead of a delete operation, which can take a while and potentially introduce some table locking.

truncate table dbo.stagingtable1

Now, keep in mind that this example does not factor any indexes, primary keys, etc., so when you apply this to your environment, maintain your PK and indexing strategies accordingly.

You can download the full demo script here.


Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.