5

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?

4
  • 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. Aug 28, 2014 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, 2014 at 18:56
  • Hi, did you find the solution? I need the exact same thing. Feb 23, 2016 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. Nov 7, 2016 at 10:00

3 Answers 3

7

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@[email protected]'', 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 'before $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
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
2
  • 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? Aug 28, 2014 at 12:18
  • 1
    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, 2014 at 12:43
3

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.

3
  • 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? Aug 28, 2014 at 12:19
  • Or will this issue be resolved with partition switching, and a staging table? Aug 28, 2014 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. Aug 28, 2014 at 13:05
1

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 @DATEADD AS INTEGER;
DECLARE @ENDDATE DATETIME2;

SET @DATEADD = 1;
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.