0

I have a Sql Server logging table, that continuously is being inserted into, I want to revise the design of this table.

Currently, we are using a GUID as the PK, because the software can generate this and we just insert it, and hopefully, multiple inserts won't block each other. However, UniqueId is not a very efficient PK for a table and our indexes are very large and fragmented.

If I change this column to a long with auto-increment enabled. Will this become a concurrent insert bottleneck?

I do not need my primary key to be in any sort of order, Just a unique id for that row?

Zapnologica
  • 779
  • 4
  • 9
  • 19
  • 1
    Will there be multiple sessions inserting concurrently? – Michael Green May 07 '20 at 12:50
  • Yes, there will be, multiple servers will be inserting into this table at the same time. – Zapnologica May 07 '20 at 13:37
  • 2
    There will be blocking if there are multiple sessions inserting, but whether that blocking is severe enough to be a problem--no way for us to know. It would depend on how fast the inserts are occurring, the hardware, the number of columns, the number of indexes that have to be updated, etc. Generating the identity values isn't likely to cause the blocking to be significantly worse than it would be otherwise because that is a small % of the overhead of an insert. – Tony Hinkle May 07 '20 at 19:11

1 Answers1

2

If I change this column to a long with auto-increment enabled. Will this become a concurrent insert bottleneck?

The answer is NO because the step when SQL Server gets next identity value is outside of transaction. This is why when you perform ROLLBACK identity does not decrease and you can have gaps.

Please see answer to similar question here

Artashes Khachatryan
  • 1,461
  • 1
  • 12
  • 23
  • aah I see, that is cleaver. Ok so it is optimised for that exact case. basically has a counter in memory per table. So my best bet would be to use Int IDENTITY as the primary key. – Zapnologica May 07 '20 at 13:39