Thursday, May 26, 2016

SQL Server - Example of converting a large table into partitioned table

We had a 4Tb table that I converted to a partitioned table. The conversion took around 30 hours.

Partitioning was laid out over 16 filegroups, but I'll make an example for 4 filegourps.
We used computed column as a partition key.

=================================================
Add filegroups
=================================================
ALTER DATABASE DBDB ADD FILEGROUP COMMON1
ALTER DATABASE DBDB ADD FILEGROUP COMMON2
ALTER DATABASE DBDB ADD FILEGROUP COMMON3
ALTER DATABASE DBDB ADD FILEGROUP COMMON4

=================================================
Add Files to filegroups
=================================================
-- Expected size: 9G
ALTER DATABASE DBDB
ADD FILE
 ( NAME = COMMON1,  FILENAME = 'F:\Data1\SQL\DBDB\common1.mdf', SIZE = 1GB, FILEGROWTH = 51200KB ),
 ( NAME = COMMON2,  FILENAME = 'F:\Data2\SQL\DBDB\common2.mdf', SIZE = 1GB, FILEGROWTH = 51200KB ),
etc...
TO FILEGROUP COMMON1
go

-- Check them with
select fileid, g.groupid, groupname, f.filename  from sysfiles f, sysfilegroups g
where f.groupid = g.groupid order by 3,1

=================================================
Partition the table
=================================================
DROP PRTITION SCHEME DBDBPartitioning;
DROP PARTITION FUNCTION DBDBPartitioning;
CREATE PARTITION FUNCTION DBDBPartitioning (int)
AS RANGE LEFT FOR VALUES (
199999999 ,
299999999 ,
399999999
)
go

DROP PARTITION SCHEME DBDBPartitioning;
CREATE PARTITION SCHEME DBDBPartitioning AS PARTITION DBDBPartitioningTO (
COMMON1 ,
COMMON2 ,
COMMON3 ,
COMMON4 ,
[primary])
go

=================================================
Add the computed key to be used later for partitioning- whatever suits you
=================================================

SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET NUMERIC_ROUNDABORT OFF
SET QUOTED_IDENTIFIER ON
-- 3 minutes to run
ALTER TABLE EMPLOYEE
ADD TYPE_DATE_COMPUTED AS
EmployeeID*100000000+year(created_date)*10000+ month(created_date)*100+day(created_date)
PERSISTED  not null
go


=================================================
Partition the table AKA Rebuild Cluster index
=================================================
-- 29h 41min
-- It uses 120G of log files
CREATE CLUSTERED INDEX [PK_DBDBPartitioning] ON [dbo].[EMPLOYEE]
(
[TYPE_DATE_COMPUTED]
)
WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF)
ON [DBDBPartitioning](TYPE_DATE_COMPUTED])
go

=================================================
Align other Document indexes
=================================================
Do this for all indexes:

CREATE UNIQUE NONCLUSTERED INDEX [IDX_UNIQUE_EMP]
ON [dbo].[EMPLOYEE]
(
[emp_ID] ASC,
[emp_TYPE_ID] ASC,
[emp_SEQUENCE] ASC,
TYPE_DATE_COMPUTED -- this needed to be added
)
WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON)
ON [DBDBPartitioning]([TYPE_DATE_COMPUTED])
go

=================================================
Make older filegroups READ ONLY
(if you need to)
=================================================
Unfortunately, to do this, no connections are allowed to the database
ALTER DATABASE DBDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE DBDB MODIFY FILEGROUP COMMON3 READONLY
ALTER DATABASE DBDB MODIFY FILEGROUP COMMON4 READONLY
ALTER DATABASE DBDB SET MULTI_USER

=================================================
Other partitioning commands I might possibly need
=================================================
-- To see into which partition # value 220161227 will go
SELECT $PARTITION.PIPSPartitioning (220161227)

No comments: