0

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?

3
  • 1
    Does the message timestamp indicate the time inserted, (in which case it's an ever increasing value, meaning ORDER BY id gives the same results as ORDER BY messageTimestamp)? Is messageTimestamp indexed?
    – AMtwo
    Nov 26, 2021 at 14:04
  • @AMtwo Same results, so long as the messageTimestamp is also unique, otherwise there can be some nondeterministic results with sorting. But probably irrelevant to the point you were getting at, just throwing it out there as caution for anyone who assumes otherwise for other contexts.
    – J.D.
    Nov 26, 2021 at 17:42
  • Yes, messageTimestamp indicate insert time. And no this is not unique and not indexed column Nov 27, 2021 at 15:09

1 Answer 1

2

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.

1
  • How to maintain partitioning over id when i need to keep this table on weekly (timely) basis. Will I need constantly increase id ranges of partioning? I'd like to create self efficient mechanizm /job to not to do anything manually. Could You please elaborate more about problems with computed column? Nov 28, 2021 at 20:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.