0

So, I have a table that typically has around 300-500K rows being inserted in to it within a 24 hour time period. Data is also continuously queried from this table. There is an Intime column that holds the date+time that the data is inserted. The data needs to be retained for a period of 3 months as of date. The end-users could also call an API that would update a status flag on all the records for a user within the same time period. TYpically we would just partition the table based on the month or day and set up a rolling partition to handle this.

But given that the data could actually be updated across partitions through a single query, is there any downside to partitioning the table. Any design suggestions on on how to handle such a table is also appreciated.

CREATE TABLE [dbo].[tbl_Message](
    [MessageId] [bigint] IDENTITY(1,1) NOT NULL,
    [MessageText] [nvarchar](max),
    [UserId] [varchar](100),
    [Status] [bit] NULL,
    [Intime] [datetime] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
Chetan
  • 443
  • 4
  • 8
  • Updating rows in a single query that happen to be in different partitions is not a concern. As far as downside is concerned, it depends on your queries. Those that don't specify `Intime` will need to touch all partitions. If `MessageId` is the primary key, you'll need to change it to a composite key with the partitioning column in order to align the indexes for the sliding window. – Dan Guzman Nov 15 '22 at 11:59
  • Thanks for that. Would updating rows across partitions create any performance issues or increase chances of a waitlock or a deadlock on the table. – Chetan Nov 15 '22 at 12:04
  • Not sure I understand your concern. Indexing is the key improving performance and concurrency regardless of whether one uses table partitioning. – Dan Guzman Nov 15 '22 at 12:08
  • Got it. That was just me wondering if the table partitioning changes anything in terms of how a table performance would be impacted despite having indexes in place or if there was some other factor that needed to be accounted for. Thanks for the quick reply. If you could please post your comment as an answer, I will mark it as accepted. – Chetan Nov 15 '22 at 12:24

1 Answers1

0

Updating rows in a single query that happen to be in different partitions is not a concern, AFAIK. Indexing is the key improving performance and concurrency regardless of whether one uses table partitioning.

Table partitioning can improve manageability of large tables with a sliding window but there are considerations before doing so. A sliding window requires alignment so all indexes must be partitioned and the partitioning column needs to be part of the primary key (presumably MessageID here) and all other unique indexes.

Also, queries that do not specify the partitioning column will need to touch all partitions. Consequently, a query like SELECT * [dbo].[tbl_Message] WHERE MessageID = 1; will need 90+ index seeks with a 3-month daily sliding a daily to return a single row. Be sure to examine execution plans to avoid performance degradation.

Dan Guzman
  • 43,250
  • 3
  • 46
  • 71