Dynamic Table partitioning on a daily basis

I have a SQL Server database which contains two tables -- Acks and Logs.

These two tables are related in logic but not in a relational database way. Basically, every message that comes in gets saved in the Log table and, if our server acknowledges it, then that ack gets stored in the Ack table.

We are storing around 5 million Acks and 3 million Logs a day. I am trying to partition these two tables on a daily boundary so that we can easily remove older partitions from the table, and also improve query performance.

I haven't done table partitioning before, so I have been reading some on-line tutorials, however I am stuck on one thing. All the tutorials I have followed seem to manually add filegroups and manually add boundaries.

I want SQL Server somehow to do this daily, and this is what my question is about. I need it to create the new filegroups for the next day, every day at, say, 22:00. Then at 24:00 the inserts should start filling up the new day's partition.

Can anyone point me in the right direction on how to achieve this? Either a comprehensive tutorial or some good old advice will do as well.

My second question: can I somehow apply the same partition function to the two different tables?

They both have a datetime(2) column on which I want to partition, and the same rules will apply.

How does that then fit in with my filegroups? Do I need a single filegroup for the day? Will each table have a file in that filegroup, or will both tables save to the same file in the filegroup?

Do I have to make a .mdf and .ldf for each filegroup? Or is there still one log file for the entire database?

• As this is a log / event tracking database. Every single query will have a start date and an end date. generally a weeks worth of data. do if in a table of 3 months data. I assume it will increase performance, or at the very least, reduce the number of entries to traverse in the query. – Zapnologica Aug 28 '14 at 12:14
• @Zapnologica, there was an article in SQL Magazine a couple of years ago describing the process. Perhaps you could adopt and apply some of it. – DenisT Aug 28 '14 at 18:56
• Hi, did you find the solution? I need the exact same thing. – Saeed Neamati Feb 23 '16 at 6:39
• @SaeedNeamati I did eventually. However it is not a very nice and simple solution, Requires endless maintenance, Horrible big scripts to create new partitions daily. I have also not got the indexing 100% correct. – Zapnologica Nov 7 '16 at 10:00

From SQL Server 2008 SP2 and SQL Server 2008 R2 SP1 there is support for 15,000 partitions so to be honest, you don't really need to do that much dynamically. Instead of having a complex daily process (Dynamically add filegroup and boundaries) with an opportunity to fail, simply create the partitions up-front from now to the year 2020, and you're well within your limits and pretty future-proof.

You could assign all partitions to one filegroup (ok not necessarily a great pattern), or round-robin between a limited number. To put it another way, there is no technical need to have a filegroup per day, eg

-- Assign all to one filegroup; ok not necessarily great
CREATE PARTITION SCHEME ps_test AS PARTITION pf_test ALL TO ( [FG1] )

-- Or round-robin
CREATE PARTITION SCHEME ps_test AS PARTITION pf_test ALL TO ( [FG1], [FG2], [FG3], [FG4], [FG5], [FG6], [FG7], [FG1] ... etc )


Obviously use Excel or some tool to generate the scripts for you - no need to type them out : )

Use DMV sys.partition_range_values and metadata function PARTITION to work out information about what data is where. Create a daily job to switch out and truncate your oldest partition. I would regard this as lower risk than the daily add. Warning!! Carefully read the whitepaper as this needs to be enabled and there are some issues with this approach ( eg Creating and rebuilding nonaligned indexes on a table with more than 1,000 partitions is not supported ). If you're feeling risk averse the standard limit of 1,000 partitions would still allow you to pre-allocate just under 3 years. As you really want to partition by DATE not DATETIME2, consider a computed column. I would probably want to performance test this first though. There is also a tool on codeplex (SQL Server Partition Management) which might be worth a look although I haven't used it. To answer your other questions, there should only be one log file for the database. Add other files as .ndf not .mdf. To use the same partition scheme (not function) on the same tables, simply create them with the same scheme, they will divide the data up into the files beneath the filegroups eg CREATE TABLE dbo.yourTable ( ... CONSTRAINT PK_yourTable PRIMARY KEY ( rowId, someDate ) ) ON ps_test(someDate)  OK, this will make for a long answer but I've knocked up a demo of how something like this might work. It does remind me that the great thing about partition switching is that as a metadata-only operation it's instant. Just to be clear, this is a demo to show off principles and some example "how-to" code, it is not production quality. Work through it and make sure you understand it before running in a dev or test environment. You'll need about 200MB space. ------------------------------------------------------------------------------------------------ -- Setup START -- Demo runs on my laptop in < 1 minute (ok on SSD) -- You'll need 200MB space ------------------------------------------------------------------------------------------------ USE master GO IF EXISTS ( SELECT * FROM sys.databases WHERE name = 'tooManyPartitionsTest' ) ALTER DATABASE tooManyPartitionsTest SET SINGLE_USER WITH ROLLBACK IMMEDIATE GO IF EXISTS ( SELECT * FROM sys.databases WHERE name = 'tooManyPartitionsTest' ) DROP DATABASE tooManyPartitionsTest GO CREATE DATABASE tooManyPartitionsTest GO ALTER DATABASE tooManyPartitionsTest SET RECOVERY SIMPLE GO -- Add 7 filegroups with 4 files each -- Add 365 files and filegroup DECLARE @fg INT = 0, @f INT = 0, @sql NVARCHAR(MAX) WHILE @fg < 7 BEGIN SET @fg += 1 SET @sql = 'ALTER DATABASE tooManyPartitionsTest ADD FILEGROUP tooManyPartitionsTestFg' + CAST( @fg AS VARCHAR(5) ) -- Add the filegroup PRINT @sql EXEC(@sql) -- Initialise SET @f = 0 WHILE @f < 4 BEGIN SET @f += 1 --!!WARNING!! DON'T USE THESE SETTINGS IN PRODUCTION. 3MB starting size and 1MB filegrowth are just for demo - would be extremely painful for live data SET @sql = 'ALTER DATABASE tooManyPartitionsTest ADD FILE ( NAME = N''tooManyPartitionsTestFile@f_@fg'', FILENAME = N''s:\temp\tooManyPartitionsTestFile@f_@fg.ndf'', SIZE = 3MB, FILEGROWTH = 1MB ) TO FILEGROUP [tooManyPartitionsTestFg@fg]' SET @sql = REPLACE ( @sql, '@fg', @fg ) SET @sql = REPLACE ( @sql, '@f', @f ) -- Add the file PRINT @sql EXEC(@sql) END END GO USE tooManyPartitionsTest GO SELECT * FROM sys.filegroups SELECT * FROM sys.database_files GO -- Generate partition function with ~3 years worth of daily partitions from 1 Jan 2014. DECLARE @bigString NVARCHAR(MAX) = '' ;WITH cte AS ( SELECT CAST( '30 Apr 2014' AS DATE ) testDate UNION ALL SELECT DATEADD( day, 1, testDate ) FROM cte WHERE testDate < '31 Dec 2016' ) SELECT @bigString += ',' + QUOTENAME( CONVERT ( VARCHAR, testDate, 106 ), '''' ) FROM cte OPTION ( MAXRECURSION 1100 ) SELECT @bigString = 'CREATE PARTITION FUNCTION pf_test (DATE) AS RANGE RIGHT FOR VALUES ( ' + STUFF( @bigString, 1, 1, '' ) + ' )' SELECT @bigString bs -- Create the partition function PRINT @bigString EXEC ( @bigString ) GO /* -- Look at the boundaries SELECT * FROM sys.partition_range_values WHERE function_id = ( SELECT function_id FROM sys.partition_functions WHERE name = 'pf_test' ) GO */ DECLARE @bigString NVARCHAR(MAX) = '' ;WITH cte AS ( SELECT ROW_NUMBER() OVER( ORDER BY boundary_id ) rn FROM sys.partition_range_values WHERE function_id = ( SELECT function_id FROM sys.partition_functions WHERE name = 'pf_test' ) UNION ALL SELECT 1 -- additional row required for fg ) SELECT @bigString += ',' + '[tooManyPartitionsTestFg' + CAST( ( rn % 7 ) + 1 AS VARCHAR(5) ) + ']' FROM cte OPTION ( MAXRECURSION 1100 ) SELECT @bigString = 'CREATE PARTITION SCHEME ps_test AS PARTITION pf_test TO ( ' + STUFF( @bigString, 1, 1, '' ) + ' )' PRINT @bigString EXEC ( @bigString ) GO IF OBJECT_ID('dbo.yourLog') IS NULL CREATE TABLE dbo.yourLog ( logId INT IDENTITY, someDate DATETIME2 NOT NULL, someData UNIQUEIDENTIFIER DEFAULT NEWID(), dateAdded DATETIME DEFAULT GETDATE(), addedBy VARCHAR(30) DEFAULT SUSER_NAME(), -- Computed column for partitioning? partitionDate AS CAST( someDate AS DATE ) PERSISTED, CONSTRAINT pk_yourLog PRIMARY KEY ( logId, partitionDate ) -- << !!TODO try other way round ) ON [ps_test]( partitionDate ) GO IF OBJECT_ID('dbo.yourAcks') IS NULL CREATE TABLE dbo.yourAcks ( ackId INT IDENTITY(100000,1), logId INT NOT NULL, partitionDate DATE NOT NULL CONSTRAINT pk_yourAcks PRIMARY KEY ( ackId, logId, partitionDate ) ) ON [ps_test]( partitionDate ) GO IF OBJECT_ID('dbo.yourLogSwitch') IS NULL CREATE TABLE dbo.yourLogSwitch ( logId INT IDENTITY, someDate DATETIME2 NOT NULL, someData UNIQUEIDENTIFIER DEFAULT NEWID(), dateAdded DATETIME DEFAULT GETDATE(), addedBy VARCHAR(30) DEFAULT SUSER_NAME(), -- Computed column for partitioning? partitionDate AS CAST( someDate AS DATE ) PERSISTED, CONSTRAINT pk_yourLogSwitch PRIMARY KEY ( logId, partitionDate ) ) ON [ps_test]( partitionDate ) GO -- Setup END ------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------ -- Data START ------------------------------------------------------------------------------------------------ -- OK load up data for Jan 2014 to today. DECLARE @startDate DATETIME = '1 Jan 2014', @rand INT WHILE @startDate < GETDATE() BEGIN -- Add between 1 and 10,000 rows to dbo.yourLog for today SET @rand = RAND() * 10000 ;WITH cte AS ( SELECT TOP 10000 ROW_NUMBER() OVER ( ORDER BY ( SELECT 1 ) ) rn FROM master.sys.columns c1 CROSS JOIN master.sys.columns c2 CROSS JOIN master.sys.columns c3 ) INSERT INTO dbo.yourLog (someDate) SELECT TOP(@rand) DATEADD( second, rn % 30000, @startDate ) FROM cte -- Add most of the Acks INSERT INTO dbo.yourAcks ( logId, partitionDate ) SELECT TOP 70 PERCENT logId, partitionDate FROM dbo.yourLog WHERE partitionDate = @startDate SET @startDate = DATEADD( day, 1, @startDate ) CHECKPOINT END GO -- Have a look at the data we've loaded SELECT 'before yourLog' s, COUNT(*) records, MIN(someDate) minDate, MAX(someDate) maxDate FROM dbo.yourLog SELECT 'before yourAcks' s, COUNT(*) records, MIN(partitionDate) minDate, MAX(partitionDate) maxDate FROM dbo.yourAcks -- You'll see how pre-May data is initially clumped together SELECT 'beforepartition' s, $PARTITION.pf_test( partitionDate ) p, MIN(partitionDate) xMinDate, MAX(partitionDate) xMaxDate, COUNT(*) AS records FROM dbo.yourLog WITH(NOLOCK) GROUP BY$PARTITION.pf_test( partitionDate )
ORDER BY xMinDate
GO

-- Data END
------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------
-- Maintenance START
------------------------------------------------------------------------------------------------

-- Oh man, we're behind with our switching and truncation.
-- Create a job that sweeps up.  Do we get blocking?

-- ALTER TABLE dbo.yourLog SWITCH PARTITION 1 TO dbo.yourLogSwitch PARTITION 1
-- TRUNCATE TABLE dbo.yourLogSwitch

-- Let's pretend we only want to maintain up to 30 days ago
DECLARE @testDate DATE
SET @testDate = DATEADD( day, -30, GETDATE() )

-- Create local fast_forward ( forward-only, read-only ) cursor
DECLARE partitions_cursor CURSOR FAST_FORWARD LOCAL FOR
SELECT boundary_id, CAST( value AS DATE )
FROM sys.partition_range_values
WHERE function_id = ( SELECT function_id FROM sys.partition_functions WHERE name = 'pf_test' )
AND value < @testDate

-- Cursor variables
DECLARE @boundary_id INT, @value DATE, @sql NVARCHAR(MAX)

OPEN partitions_cursor

FETCH NEXT FROM partitions_cursor INTO @boundary_id, @value
WHILE @@fetch_status = 0
BEGIN

-- Switch out and truncate old partition
SET @sql = 'ALTER TABLE dbo.yourLog SWITCH PARTITION ' + CAST( @boundary_id AS VARCHAR(5) ) + ' TO dbo.yourLogSwitch PARTITION ' + CAST( @boundary_id AS VARCHAR(5) )

PRINT @sql
EXEC(@sql)

-- You could move the data elsewhere from here or just empty it out
TRUNCATE TABLE dbo.yourLogSwitch

--!!TODO yourAcks table

FETCH NEXT FROM partitions_cursor INTO @boundary_id, @value
END

CLOSE partitions_cursor
DEALLOCATE partitions_cursor
GO

-- Maintenance END
------------------------------------------------------------------------------------------------

-- Have a look at the data we've maintained
SELECT 'after yourLog' s, COUNT(*) records, MIN(someDate) minDate, MAX(someDate) maxDate FROM dbo.yourLog
SELECT 'after yourAcks' s, COUNT(*) records, MIN(partitionDate) minDate, MAX(partitionDate) maxDate FROM dbo.yourAcks

-- You'll see how pre-May data is initially clumped together
SELECT 'after $partition' s,$PARTITION.pf_test( partitionDate ) p, MIN(partitionDate) xMinDate, MAX(partitionDate) xMaxDate, COUNT(*) AS records
FROM dbo.yourLog WITH(NOLOCK)
GROUP BY \$PARTITION.pf_test( partitionDate )
ORDER BY xMinDate

-- Remember, date must always be part of query now to get partition elimination
SELECT *
FROM dbo.yourLog
WHERE partitionDate = '1 August 2014'
GO

-- Cleanup
USE master
GO

IF EXISTS ( SELECT * FROM sys.databases WHERE name = 'tooManyPartitionsTest' )
ALTER DATABASE tooManyPartitionsTest SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
IF EXISTS ( SELECT * FROM sys.databases WHERE name = 'tooManyPartitionsTest' )
DROP DATABASE tooManyPartitionsTest
GO

• I like your approach / thoughts to wards the partitions. However not very elegant. I see it to be a way more practical and easier / time saving way to go about it. I definably think I will start off this way. Will there be no overhead of having so many files per file group? – Zapnologica Aug 28 '14 at 12:18
• The relationship between filegroups and files is 1-n, ie one filegroup can have one or many files. The potential issue with this design I suppose is one filegroup having many partition boundaries, I'll try and spin up an example, work it through. – wBob Aug 28 '14 at 12:43

To add a new partition, use SPLIT RANGE. Assuming you have the following partition:

CREATE PARTITION FUNCTION pfTest(int) AS RANGE LEFT FOR VALUES (10, 20, 30);
CREATE PARTITION SCHEME psTest AS PARTITION pfTest TO ([GRP1], [GRP2], [GRP3]);


.. you can add a new partition by "splitting" the last range from (30 to infinity) to (30-39) and (40 to infinity). Here's the syntax:

ALTER PARTITION FUNCTION pfTest() SPLIT RANGE (40);
ALTER PARTITION SCHEME psTest NEXT USED [GRP4];


I don't know of any other way to do this automatically than to generate dynamic SQL and run it on a schedule, for instance in a SQL Server Agent job.

Partition functions can be applied to as many tables as you want. Each table can be placed on a partition scheme, which in turn is connected to a partition function.

--- Create the partition function:
CREATE PARTITION FUNCTION fn_part_left(int)
AS RANGE LEFT FOR VALUES (100, 110, 120, 130);

--- Create the partition scheme:
CREATE PARTITION SCHEME ps_part_left AS
PARTITION fn_part_left TO
([GROUP_A], [GROUP_B], [GROUP_C], [GROUP_A], [GROUP_B]);

--- Create the table
CREATE TABLE myTable (
someColumn int NOT NULL,
....
) ON [ps_part_left](someColumn);


I've used "int" as a datatype in my example, but datetime2 will work as well.

You can place more than one partition on the same file group if you want. Here, you'll have to do some planning with regards to how the load is distributed across different partitions, so you don't put all the I/O load on a single filegroup.

A filegroup can have one or more .mdf files.

A database can have one or more .ldf file.

• So if I wanted to remove an old partition every day, and move it off to an archive server. Would I then need a separate log file for each file group? so that when I remove it from the live database, i can import it into the archive database? – Zapnologica Aug 28 '14 at 12:19
• Or will this issue be resolved with partition switching, and a staging table? – Zapnologica Aug 28 '14 at 12:26
• You could absolutely switch out the partitions into a history table (partitioned or non-partitioned). From there, you could move the data to another database or another server. When switching, however, the source and target have to be in the same filegroup. – Daniel Hutmacher Aug 28 '14 at 13:05

So, this is something I've been experimenting with for a while. Here's my shot at creating a dynamic script by table...

-- DROP TABLE #VT_TEMP;

DECLARE @CURRENTDATE AS DATETIME;
DECLARE @ENDDATE DATETIME2;

SET @ENDDATE = '21070702'; -- NEW END DATE

-- GET CURRENT BOUNDARIES
SELECT ROW_NUMBER() OVER(ORDER BY DATEKEY) AS ROW_RANK
, *
INTO #VT_TEMP
FROM STARDW.DBO.DATEDIM
WHERE DATEKEY >= 20150101 -- there is a 1007 month beginning from 2015 to     2107
AND DAYOFMONTH = 1
ORDER BY 1

;
WITH MYCTE ( R_RANK, TXT)
AS (

SELECT ROW_RANK
, CAST('CREATE PARTITION FUNCTION HZPFUN_DATEDIM (DATETIME) AS RANGE RIGHT FOR VALUES ( ' + '''' + convert(VARCHAR(10), DATEVALUE, 120) + '''' AS VARCHAR(MAX))

FROM #VT_TEMP
WHERE ROW_RANK = 1

UNION ALL

SELECT V.ROW_RANK
, CAST(TXT + ', ' + '''' +convert(VARCHAR(10), DATEVALUE, 120) + '''' AS VARCHAR(MAX))

FROM #VT_TEMP V INNER JOIN MYCTE C
ON V.ROW_RANK = C.R_RANK+1
WHERE C.R_RANK < 2000

)
SELECT *
FROM MYCTE M INNER JOIN ( SELECT MAX(R_RANK) AS MXR_RANK
FROM MYCTE
) MX
ON M.R_RANK = MX.MXR_RANK
option (maxrecursion 2000)

;

SELECT 'CREATE PARTITION SCHEME HZPSE_DATEDIM AS PARTITION HZPFUN_DATEDIM     TO (PRIMARY);'

CREATE TABLE [dbo].[OPPEProviderSummaryFact](
[OPPEProviderSummaryFactKey] [bigint] NOT NULL,
[ID] [varchar](50) NOT NULL,
[IDType] [varchar](50) NOT NULL,
[ProvId] [varchar](50) NOT NULL,
[NPI] [varchar](20) NULL,
[ProviderName] [varchar](50) NULL,
[DateKey] [bigint] NOT NULL,
[FiscalReportYrMth] [varchar](20) NOT NULL,
[ReportMonth] [datetime] NOT NULL,
[CostCenterKey] [bigint] NULL,
[DepartmentKey] [bigint] NULL,
[DepartmentName] [varchar](100) NULL,
[RevLocId] [bigint] NULL,
[RowId] [int] NULL,
[EncounterProfileKey] [bigint] NULL,
[EncounterCategoryName] [varchar](100) NULL,
[EncounterTypeCd] [varchar](20) NULL,
[TotalEncountersCnt] [int] NULL,
[DiagnsosisKey] [bigint] NULL,
[DiagnosisId] [int] NULL,
[DiagnosisName] [varchar](100) NULL,
[TotalDiagnosisCnt] [int] NULL,
[PrimaryLOSProcedureId] [int] NULL,
[ProcedureName] [varchar](100) NULL,
[TotalLOSCPTCnt] [int] NULL,
[MedicationKey] [bigint] NULL,
[MedicationId] [int] NULL,
[GenericMedicationName] [varchar](100) NULL,
[TotalMedicationCnt] [int] NULL,
[OrderClassCd] [varchar](20) NULL,
[OrderName] [varchar](100) NULL,
[TotalProceduresCnt] [int] NULL,
[InsertDt] [datetime] NULL,
[StartDt] [datetime] NOT NULL,
[EndDate] [datetime] NULL,
[isCurrent] [char](1) NULL,
[HashedCheckField] [nvarchar](50) NULL
CONSTRAINT HPPK_OPPEProviderSummaryKey PRIMARY KEY (id,ProvId,StartDt)
) ON HZPSE_DATEDIM (StartDt)
;


Note: I haven't fully tested it yet but I think its close.