Extra precautions that needs to be taken while performing partitioning in real time

Hi All,

Today I am going to share a scenario where I need to partition the table on production server for the existing table. Before reading this I have written few posts on partitioning do read them if you would like to.

Despite of working on many scenarios in my laptop I still faced few issues while doing in real time.

To mimic this I have created a test table and loaded the data. Below are the details

CREATE TABLE [dbo].[TABLE1] 
([pkcol] [int] identity NOT NULL,
 [datacol1] [int] NULL,
 [datacol2] [int] NULL,
 [datacol3] [varchar](50) NULL,
 [partitioncol] datetime)
GO

ALTER TABLE dbo.TABLE1 ADD CONSTRAINT PK_TABLE1 PRIMARY KEY NONCLUSTERED  (pkcol)
   WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 
         ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

I created table with identity column and primary key on PKCOl column and then populated the data with the below queries

set nocount on 
DECLARE @val INT
SELECT @val=1000
WHILE @val < 2000
BEGIN  
   INSERT INTO dbo.Table1(pkcol, datacol1, datacol2, datacol3, partitioncol) 
      VALUES (@val,@val,@val,'TEST',getdate())
   SELECT @val=@val+1
END
GO

set identity_insert table1 on
set nocount on 
DECLARE @val INT
SELECT @val=2000
WHILE @val < 3000
BEGIN  
   INSERT INTO dbo.Table1(pkcol, datacol1, datacol2, datacol3, partitioncol) 
      VALUES (@val,@val,@val,'TEST',getdate()+7)
   SELECT @val=@val+1
END
GO

set identity_insert table1 on
set nocount on 
DECLARE @val INT
SELECT @val=3000
WHILE @val < 4000
BEGIN  
   INSERT INTO dbo.Table1(pkcol, datacol1, datacol2, datacol3, partitioncol) 
      VALUES (@val,@val,@val,'TEST',getdate()+14)
   SELECT @val=@val+1
END
GO



After populating the data looks like above. In my case the primary key is already in place but I need to create partition on some other column hence I was supposed to drop the primary key and create it as NON CLUSTERED and then create the clustered index on the partition scheme.

Now let's create partition function and partitions scheme

CREATE PARTITION FUNCTION myDateRangePF_test (datetime)
AS RANGE RIGHT FOR VALUES (
'2020-06-15 00:00:00.000',
'2020-06-22 00:00:00.000',
'2020-06-29 00:00:00.000',
'2020-07-06 00:00:00.000'
)
GO


CREATE PARTITION SCHEME myPartitionScheme_test 
AS PARTITION myDateRangePF_test ALL TO ([PRIMARY]) 
GO

Now we are going to create clustered index on the table

 CREATE CLUSTERED INDEX IX_TABLE1_partitioncol ON dbo.TABLE1 (partitioncol)
  WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 
        ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) 
  ON myPartitionScheme_TEST(partitioncol)

After doing the data looks like below



While I was doing it on production it's a huge table and I need to partition the data on weekly basis.
 Lets say now you need to switch out the data from the 2nd partition to staging table. Also I am running on SQL Server 2012 hence I don't have an option of truncate partition.

So I need to create staging table to have similar metadata to that of main table and below is the script for the same

CREATE TABLE [dbo].[TABLE1_work] 
([pkcol] [int] identity NOT NULL,
 [datacol1] [int] NULL,
 [datacol2] [int] NULL,
 [datacol3] [varchar](50) NULL,
 [partitioncol] datetime)
GO

ALTER TABLE dbo.TABLE1_work ADD CONSTRAINT PK_TABLE1_work PRIMARY KEY NONCLUSTERED  (pkcol)
   WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 
         ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]


CREATE CLUSTERED INDEX IX_TABLE1_partitioncol_work ON dbo.TABLE1_work (partitioncol)
  WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 
        ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) 
  ON myPartitionScheme_TEST(partitioncol)

  As this doesn't have any rows here is how the partition looks like


Now this was the setup what I have made before the partitioning and there was no downtime given to me as they said it is 24*7 based application. The only one savior in my case is it is an error logging table and hence it is okay to loose few records.

As everything is in place I decided to move the rows so fired the command

Alter table table1 switch partition 2 to table1_work partition 2

Boom, got the below error. Wow, this table has full text in place which I am not even aware of

Msg 4918, Level 16, State 1, Line 1
ALTER TABLE SWITCH statement failed because the table 'piscertify_test.dbo.table1' has fulltext index on it.

For your information we need to have primary key on the table to have full text enabled.

In my case I dropped the full text and then dropped primary key which was created as clustered and recreated it as NON Clustered.

To be able to perform switch out we need to have identical stuff to that of source .
Hence you need to enable Full text index even on staging table which can be done using below query

 USE [DBNAME]
GO
CREATE FULLTEXT INDEX ON [dbo].[TABLE1_work] KEY INDEX [PK_TABLE1_work] ON ([piscertify_catalog]) WITH (CHANGE_TRACKING AUTO)
GO
USE [DBNAME]
GO
ALTER FULLTEXT INDEX ON [dbo].[TABLE1_work] DISABLE
GO

Now try to fire the alter switch command see if that works. Again it failed

Msg 4918, Level 16, State 1, Line 1
ALTER TABLE SWITCH statement failed because the table 'DBNAME_test.dbo.table1' has fulltext index on it.

So we need to drop the full text index and then perform switch post that enable the full text back

DROP FULLTEXT INDEX ON [dbo].[table1]

DROP FULLTEXT INDEX ON [dbo].[table1_work]

Don't forget to disable non clustered index on both source and target if it is non-aligned otherwise you will get error like below

Msg 7733, Level 16, State 4, Line 1
'ALTER TABLE SWITCH' statement failed. The table 'DBNAME_test.dbo.table1' is partitioned while index 'PK_TABLE1' is not partitioned.

Once after performing all the above steps now the switch statement will work as expected


As you can see now the rows(999) from the 2nd partition has been moved to staging table.

Well imagine for some reason you wanted to have your rows back from staging to original table then you can do switch in like below

alter table table1_work switch partition 2 to table1 partition 2

But to do this the source table must not have any rows if not you will get the below error 

Msg 4904, Level 16, State 1, Line 1
ALTER TABLE SWITCH statement failed. The specified partition 2 of target table 'DBNAME_test.dbo.table1' must be empty.

Well I have to do this disabling of non clustered and dropping of full text and switch out and then rebuild followed by enabling of full TEXT.

What we are doing this on a weekly basis is called as "partitioning sliding Window" which can be automated using the below link  as reference.


Lessons Learned:

1) Check if Full text search is enabled for the table that you are going to partition with
2) when we disable the non clustered index it can be enabled only by rebuild hence make use of (ONLINE=ON) to avoid lockings
3) For some reason if you want to switch back the rows from staging table to main table ensure that partition is empty

Comments