How to reduce table partition timing for existing table in SQL Server

by kannadhasan G   Last Updated January 11, 2018 11:06 AM

Repost from:

I have a problem with table partition, I am working in a very big database, It is around 2 terabyte. So we planned to split the large tables into different partitions at last year. On that time we were divided a table into two partitons. One partition was located in primary file group and another one was located in secondary file group named as PartitionFG1. So we got lots of benefits from this activity like index maintenance and performance.

And then we have planned to create another one partition for the same table for this upcoming year records. So we have created a new secondary file group and we have altered the partition scheme to use new secondary file group named as PartitionFG2. So we have alter the partition function to split the range, here is the problem. Here the new range value split and moves the data into new partition but not into the new file group. For example table’s max PK value is 1000, so I used split range value as 1000. So it should not be taken any time to move data to new partition, because new partition will contains 0 records only.

Now I am going describe the scenario by sequence.

Table Creation

CREATE TABLE Tbl_3rdParatition(PK NUMERIC(18,0) identity(1,1),Line Varchar(100))

CREATE CLUSTERED INDEX CidxTbl_3rdParatition ON Tbl_3rdParatition (pk ASC)

Insert record into table


WHILE (@I< = @CNT)
  INSERT INTO Tbl_3rdParatition (Line) VALUES ('Primary')
  SET @I=@I+1

Now we have inserted 1000 records into the table. We can verify the table’s row and partition by below query.

SELECT p.partition_number,, 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('Tbl_3rdParatition')

Record stored in Primary file group

So it ensures primary file group contains all records of the table. Now I am going to split the table into 2 partitions.


CREATE PARTITION SCHEME PS_Tbl_3rdParatition AS PARTITION PF_Tbl_3rdParatition TO ([PRIMARY],[SWPPartitionFG1])

CREATE UNIQUE CLUSTERED INDEX CidxTbl_3rdParatition ON dbo.Tbl_3rdParatition(PK) WITH(DROP_EXISTING = ON)ON PS_Tbl_3rdParatition(PK) ;

Partition1 stored in SWPPartitionFG1

Now it is divided into 2 partitions. It took some time to move 500 records to SWPPartitionFG1.

Now I am going to create a new partition on another file group.



ALTER INDEX [CidxTbl_3rdParatition] ON [dbo].[Tbl_3rdParatition ] REBUILD  WITH (SORT_IN_TEMPDB = OFF, ONLINE = ON, MAXDOP = 1)

As of above query, 3rd partition should have 0 records. That is correct. But 3rd partition should be stored in SWPPartitionFG2 correct? But second partition’s data fully moved into SWPPartitionFG2. And the 3rd partition is allocated in SWPPartitionFG1 it is wrong!. So it takes too much of time to transfer the data from FG1 to FG2.

3rd Partition stored in SWPPartitionFG1

2nd partition moved to SWPPartitionFG2

I desired to store the 3rd partition data in third file group (SWPPartitionFG2). Then only it will not take large time in partitioning process. For this reason we want lots of time to create a new partition. Our client will not give that much of down time for us.

Our Actual table size is 300 GB. 2nd partitions SWPPartitionFG1 File group contains 200 GB of data. So it requires 2:30 hrs time to move the data From FG1 to FG2. Please help me reduce the time in this activity.

Related Questions

Inefficient queries on partitioned tables

Updated May 29, 2015 12:02 PM

How to fetch data from past 2 week in SQL?

Updated July 19, 2017 20:06 PM

How to get accurate row counts in partitions in a table

Updated September 05, 2017 12:06 PM