-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,068
  • 3
  • 38
  • 67

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,068
  • 3
  • 38
  • 67
-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
  • 733
  • 5
  • 13