SQLTeam.com | Weblogs | Forums

SQL Server Table Partitioning takes longer duration while creating missing partitions or creating new empty partition


#1

Hello,

W‌e have table EVENT with rows around 216742302, this table was partitioned till 2015-01-01, partitions are missing from 2015-01 to 2017-02. If we try to create missing partitions it will take days to create missing partitions due to data size. so we decided to create new partition (2017-03) and skip the missing partitions (2015-01 to 2017-02) but this step as well taking longer duration. It looks SQL server will do the data movement for next partition as well.

w‌hat could be the fasted way to create new empty partition where we can skip the missing partitions.‌

b‌elow command used to create new partition.

[code language="sql"]‌
-‌- Table
CREATE TABLE [EVENT](
[ID] [int] IDENTITY(1,1) NOT NULL,
[MESSAGE_ID] nvarchar NULL,
[EVENT_TYPE] nvarchar NOT NULL,
[EVENT_TIME] [datetime] NOT NULL
CONSTRAINT [PK_EVENT_LOG] PRIMARY KEY NONCLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = OFF) ON [PRIMARY]
)

GO‌
-- Implement partition for the given parameter
ALTER PARTITION SCHEME partitioning_Scheme NEXT USED [dbdata03]
ALTER PARTITION FUNCTION partitioning_function() SPLIT RANGE ( '2017-03-01' )‌

‌‌[/code]‌‌