0

In our system there is a Schedule table which has two parts, one is being extensively updated (fields LockedBy,LockExpirationTime,NextDueTime), and second is mostly static (fields like Enabled,Name,SchedulePartition). In a such configuration does it make sense to split it actually in two different tables, with one to one mapping?

The update stored proc competes for schedules. It modifies only those dynamic fields, but takes into consideration static fields (such as enabled), currently it uses rowlock with readpast. There are other tables, which rely on static part of the table, thus there are many read queries to the static part.

Mikl X
  • 1,199
  • 11
  • 17
  • How large is the table? – smnbbrv May 07 '15 at 06:28
  • I would normally split tables into two or more 1:1 related tables when the table gets very wide (i.e. has many columns). It is hard for me to have to deal with tables with too many columns. For big orgs such tables can have more than 200 columns. Another thing you can consider is full table scan is slow with such big tables. I'm only worried for redundancy, because there may be more than one columns repeated in both tables. – ThePravinDeshmukh May 07 '15 at 07:03
  • The row count for the schedule table is around 4000 rows. But there are a lot of update requests to the table - 150 requests per second. One of the goals I want to achieve is to avoid nolock on this table, so that long read requests can be executed. – Mikl X May 07 '15 at 16:38

0 Answers0