-1

We have an ever growing list of guid's that we need to be able to track an ever incrementing list of integer id's for. Our current implementation creates a sequence with no cache for each guid.

However, we didn't anticipate the performance hit to the database that generating thousands of sequences and selecting from them would generate. Due to the number of sequences in use at once, the plan cache is absolutely trashed. Other queries on the server are suffering from this.

Is there a better way to track an ever incrementing id for these guids that can avoid race conditions? Each guid needs to start at 1 and increment by 1 without ever skipping a number. We'll also need to be able to cut over to this new method from our current sequences.

For example, currently the guid 'F642631D-AD28-4943-8340-5E7793EB82B8' will have the sequence dbo.F642631D-AD28-4943-8340-5E7793EB82B8 created and values retrieved with select Next Value for dbo.F642631D-AD28-4943-8340-5E7793EB82B8

DForck42
  • 3,048
  • 3
  • 34
  • 65
  • 2
    "Each guid needs to start at 1 and increment by 1 without ever skipping a number." Y tho? –  Oct 12 '21 at 20:47
  • 1
    I'm not sure that this would solve all your problems, but I don't know what would either. It seems like you're going to have to make some tradeoffs no matter what you choose. Sequence Tables – Erik Darling Oct 12 '21 at 21:33
  • @bbaird couldn't tell you, but that's what was decided in the initial design doc years ago – DForck42 Oct 13 '21 at 13:27
  • During investigation i remembered that Option (Recompile) as a thing. In our dev environment it looks promising, so we're going to look at pushing that up. – DForck42 Oct 13 '21 at 13:45
  • OPTION (RECOMPILE) will still cache a plan, it just won't be reused. – Erik Darling Oct 13 '21 at 17:02
  • 1
    If they can't have gaps, why don't you just store the max value per Guid? You could issue a locked update on such a table UPDATE Guids WITH (HOLDLOCK, ROWLOCK) SET MaxId += @toIncrement OUTPUT inserted.MaxId WHERE Guid = @guid; – Charlieface Oct 13 '21 at 23:17

2 Answers2

-1

So not 100% an answer to the question I posted, but we ended up implementing option recompile and it stopped the plan cache issues we were having

DForck42
  • 3,048
  • 3
  • 34
  • 65
-2

Did you rule out using IDENTITY columns? LINK

In a IDENTITY field, you specify the starting value and then how much to increment each time, the most common values are (1,1), which seems to match your criteria.

It looks like you can add one after the fact too, tried it out with a temp table and it seemed to work, see example below:


/* Add IDENTITY to existing table */
DROP TABLE IF EXISTS #A

CREATE TABLE #A 
(
Col1 VARCHAR(100) NOT NULL PRIMARY KEY CLUSTERED,
LoadDate DATETIME DEFAULT GETDATE()
)


INSERT INTO #A(Col1)
SELECT name FROM sys.databases


ALTER TABLE #A
ADD ID INT IDENTITY (1,1)


SELECT


    

  • 



FROM
#A

It will take time for larger tables, and will take out a schema modification lock, so you may be better off creating new tables and loading them.

BCM
  • 723
  • 5
  • 13
  • 1
    Identity values may have gaps. Note in the question "needs to start at 1 and increment by 1 without ever skipping a number". – Dan Guzman Oct 12 '21 at 20:44
  • But SEQUENCES are the current solution, which also have gaps. But IDENTITY isn't going to help. – David Browne - Microsoft Oct 12 '21 at 22:42
  • @DanGuzman For my own understanding, in what case would an identity have a gap that occurred naturally?...by naturally I mean, wasn't specifically a result of a user action, such as a developer setting IDENTITY_INSERT TableName ON. – J.D. Oct 13 '21 at 00:00
  • 2
    @J.D., See Unexpected gaps in IDENTITY column. – Dan Guzman Oct 13 '21 at 01:46
  • @DanGuzman Ahh very interesting, much appreciated! – J.D. Oct 13 '21 at 02:18
  • for a while now identities in sql server have been sequences as far as i am aware. by default, identities will cache 1k records and pull off of that when a new one is needed, and those can be lost if the server is restarted (and maybe failed over). sequences do the same by default unless you specify no cache – DForck42 Oct 13 '21 at 13:30
  • @DForck42, ah OK, I was thinking you may have had issues trying to manually maintain all the individual sequences, good to know about the possibility of gaps with identity, I had never seen that without user deletes, so that was interesting to learn. The Aaron Bertrand answer J.D. linked to may have a good solution for you. – BCM Oct 13 '21 at 13:34
  • @BCM no prob, taking a look at them now – DForck42 Oct 13 '21 at 13:35