Partitioning enhancements working with SQL Server 2016

By: Pinal Dave


One of the primary requirements inside databases is the ability to scale the system as the data volumes and the number of users accessing the system increases appreciably. There are a number of ways in which this can be achieved. One of the touted and inbuilt capability that was introduced in SQL Server 2005 was the ability to do partitioning on a specific table. Though in its initial introductions, there were enough challenges like ability to build using TSQL only, 1000 partitions only etc – but most of the restrictions were all done in the next couple of versions. I have loved the way such capabilities have been introduced over the years.

When SQL Server 2016 was introduced, I never felt there would be much on this fronts because the innovations that had to happen have already happened. What can one introduce or enhance in this space? I was pleasantly surprised of the fact that there was something interesting that was introduced that caught my eyes.

Look at a scenario as mentioned below. I have a large transactions table which is nicely partitioned across years. One of the requirement is to archive and move data beyond a certain point in time away from a table because a backup has been taken. In the past, I have seen people move the data to a separate table using the SWITCH mechanism and then they go about truncating or dropping the table it has been moved into.

Now that we have set the stage for this scenario – in SQL Server 2016 we allow you to truncate a partition alone. This was a welcome enhancement that I would like to show as part of this blog. Let me walk through the script:

DROP DATABASE IF EXISTS TruncDB;
GO
CREATE DATABASE TruncDB
ON PRIMARY
(NAME='TruncDB_Part1', FILENAME= 'C:\Data\TruncDB_Part1.mdf',
SIZE=2, MAXSIZE=100, FILEGROWTH=1), 
FILEGROUP TruncDB_Part2
(NAME = 'TruncDB_Part2', FILENAME = 'C:\Data\TruncDB_Part2.ndf',
SIZE = 2, MAXSIZE=100, FILEGROWTH=1); 
GO

USE TruncDB;
GO
--- Step 2 : Create Partition Range Function
CREATE PARTITION FUNCTION TruncDB_PartitionRange (INT)
AS RANGE LEFT FOR VALUES (10);
GO

--- Step 3 : Attach Partition Scheme to FileGroups
CREATE PARTITION SCHEME TruncDB_PartitionScheme
AS PARTITION TruncDB_PartitionRange TO ([PRIMARY], TruncDB_Part2);
GO

Now that our basics are done for setup. Let us create a table and add some values inside each of the partitions under question.

--- Step 4 : Create Table with Partition Key and Partition Scheme
CREATE TABLE TestTable
(ID INT NOT NULL,
Date DATETIME)
ON TruncDB_PartitionScheme (ID);
GO

--- Step 5 : Insert Data in Partitioned Table
INSERT INTO TestTable (ID, Date) -- Inserted in Partition 1
VALUES (1,GETDATE());
INSERT INTO TestTable (ID, Date) -- Inserted in Partition 2
VALUES (11,GETDATE());
INSERT INTO TestTable (ID, Date) -- Inserted in Partition 2
VALUES (12,GETDATE());
GO
--- Step 6: Test Data from TestTable
SELECT *
FROM TestTable;
GO

To show the before and after effect of truncate table. Here is the script that I have used, wherein we delete only the 2nd partition.

--- Step 7 : Verify Rows Inserted in Partitions
SELECT *
FROM sys.partitions
WHERE OBJECT_NAME(OBJECT_ID)='TestTable';
GO

-- Works from SQL 2016 only
TRUNCATE TABLE dbo.TestTable WITH (PARTITIONS (2));


--- Step 7 : Verify Rows Inserted in Partitions
SELECT *
FROM sys.partitions
WHERE OBJECT_NAME(OBJECT_ID)='TestTable';
GO

Partitioning enhancements Image

As you can see the # of rows is now 0 as opposed to the previous value of 2. This is an interesting and nice little enhancement that got introduced with SQL Server 2016 under the partitioning feature. We wouldn’t see much of talk on these enhancements as these are hidden well into the product.

Will such innovations and enhancements help you in your production or dev environments? I would be curious to understand how you used partitioning inside SQL Server in your environments. Have you used the 15000 partitions? What do you do? Let me know via comments.

Comments

  1. Hi Pinal,

    That’s an interesting feature to add , thanks for sharing it, much appreciate it.
    what is your opinion on Partitioning an existing large table by choosing the existing Primary Key as a partition column (mostly ID’s with data type Int) , i prefer to do it in order to avoid performance impacts on choosing a date time value as this will be converted to a clustered index to become a partiiotn Key, please share your experiences.

    Regards,
    Kiran

    • Hi Kiran,

      Thank you for your comment. I totally agree with your thought process and recently I have implemented such a logic at one of my customer where we had performance issues.

      Using Int is always a good idea.

  2. Hi Pinal,

    Great article as usual.

    I want to partition on large table but I am afraid of poor performance after partitioning as we have distinct and group by query also. Should I partition this table ?

    Thanks & Regards,
    Himanshu

  3. I have requirement in partitioning, In my environment daily we scheduled job to create partitioning which is daily partitioning for two tables.. So far 145 partition created I have a requirement like customer need only latest 90 days partition which means latest 90 days data. I need Scripts to do this step by step method( It could be very useful if we schedule job in weekly basis.). Could you please help on this. Thank you.
    SQL version : 2016 Enterprise edition

  4. Hi Pinal,
    My requirement is to create/maintain 7 days dynamic daily partition (greater than sysdate) and all other data (greater than or less than sysdate) in monthly partition.Need to auto slide the partitions(daily and monthly when required).Please provide me T-sql script or provide me logic/link for this type of task.I have a scenario where 7 days forward data is very important and needs daily partition while all other data can be partitioned monthly.
    Thanks in advance

Leave a Reply