MSSQL Large table rolling using partitions

Question

I have a large DB (6TB) consisting of 3 tables: “messages”, “messagesDetails” , “unprocessedMessages”.

Here are simple descriptions of them:

TABLE [messages]( 

    [id] [bigint] IDENTITY(1,1) NOT NULL, 

    [messageTimestamp] [datetime] NOT NULL, 

    [messageBody] [varbinary](max) NULL, 

CONSTRAINT [PK_messages] PRIMARY KEY CLUSTERED ([id] ASC) 

TABLE [messagesDetails]( 

    [id] [bigint] IDENTITY(1,1) NOT NULL, 

    [messageId] [bigint] NOT NULL 

CONSTRAINT [PK_messagesDetails] PRIMARY KEY CLUSTERED ([id] ASC) 

TABLE [messagesDetails]  WITH NOCHECK ADD CONSTRAINT [FK_messagesDetails_messages] FOREIGN KEY([messageId]) 

REFERENCES [messages] ([id]) 

 

TABLE [unprocessedMessage]( 

    [messageId] [bigint] NOT NULL 

) 

CONSTRAINT [IX_unprocessedMessage] NON CLUSTERED INDEX ([messageId] ASC) 

 

TABLE [unprocessedMessage]  WITH NOCHECK ADD CONSTRAINT [FK_unprocessedMessage_messages] FOREIGN KEY([messageId]) 

REFERENCES [messages] ([id]) 

I have 3 processes that run over those tables

1. Inserts into messages and unprocessedMessages tables - thousands per second

2. Processing messages – inserts into messagesDetails and deletes from unprocessedMessages

3. Table rolling deletes from messages and messagesDetails –runs once per day, based on message. messageTimestamp column with 28 days (4 weeks) retention

The problem is with this table rolling deletes. It must run in delete batches of less than 5000 rows per operation in order to NOT to lock the entire message table. This lasts awfully long time – about hours.

The solution that I have in mind is:

Create 53 partitions – same as the number of weeks in the year,

Create computed column on message.messageTimestamp to calculate week number and partition “messages” table based on this column.

Truncate table based with calculated partition holding date i.e. 4 weeks from the current week

My questions are:

1. Is that a good idea?

2. Is this doable on excising table or will I have to recreate the whole table from scratch?

3. Will I need more space (ex. double of current space) to move data on partitions.

4. What about the second table “messagesDetails”.

   a. Will Foreign Key on “messagesDetails” affect somehow this partitioning?

   b. How to partition “messagesDetails” to improve deleting from it?

Answer 1

Create computed column on message.messageTimestamp to calculate week number and partition “messages” table based on this column. . . .Is that a good idea?

No. Adding a computed column to drive partitioning usually does as much harm as good.

Instead partition messages by id, and the other tables by message_id. For the partition function, you can generate fixed-width ranges, like every 1M values or so.

Also messagesDetails should probably be structured like this

TABLE [messagesDetails]
( 

    [id] [bigint] IDENTITY(1,1) NOT NULL, 
    [messageId] [bigint] NOT NULL 
    . . .
)
CONSTRAINT [PK_messagesDetails] 
PRIMARY KEY CLUSTERED (messageId, id) 

This will both cluster each message's details under its messageId, and enable you to partition switch or truncate based on a range of messageIds.

Then all the tables can simply be partitioned by messageId, and you can simply truncate partitions before a selected messageId value.

It must run in delete batches of less than 5000 rows per operation in order to NOT to lock the entire message table.

Also you can disable lock escalation. See Resolve blocking problems caused by lock escalation in SQL Server. Or in combination with partitioning use partition-level lock escalation. See SQL Server 2008: Partition-level lock escalation details and examples.