This article basically focuses on the Sequence Cache Management and it’s internals. To know Sequence basics and it’s limitations you may like to visit the articles: Sequence Introduction with Extensive List of examples and Sequence Limitations and restrictions with Extensive List of examples

Sql Server provides option to specify the CACHING options while Creating/Altering Sequence. Basically Sequence created with CACHE option improves the performance by reducing the number of disk IOs that are required to generate sequence numbers.

SEQUENCE CACHE Property:

[ CACHE [<constant> ] | NO CACHE ]

WHERE:

CACHE [<constant> ]: This property can be specified if we want the Sequence with caching enabled, where <constant> is the CACHE size. If the Sequence is Created without specifying CACHE Property then by DEFAULT Sql Server creates the Sequence with CACHING option enabled and as per MSDN the Database Engine will select a size. However, users should not rely upon the selection of the CACHE size being consistent, so it is always advisable to define the required CACHE size if we want to have Sequence Caching instead of relying on the default.

NO CACHE: We can specify this option if we don’t want the Sequence to use the CACHING.

[ALSO READ] :New Features in Sql Server 2012

Let us understand in detail the Performance implications of Creating a Sequence with NO CACHE option or with CACHE enabled option.

SEQUENCE WITHOUT CACHING (i.e. Sequence created with NO CACHE option):

Below diagram demonstrates the steps are performed by Sql Server whenever Next Value is requested from a Sequence object created with NO CACHE option

So, it is clear from the above diagram that, if Sequence object created with NO CACHE option each time a next sequence value is requested it will write the calculated value to the system table before returning it to the calling statement.

[ALSO READ] Introduction to Sequence

SEQUENCE WITH CACHING:

Sequence created with CACHE option improves the performance by minimizing the no of writes to the System table for the generation of the Sequence number. In case of a sequence object created with NO CACHE option each time a next sequence value is requested, it will write the calculated value to the system table before returning it to the calling statement. But if sequence is created with CACHE of Size 50, it will write to the System table only after each 50 sequence values are served.

[ALSO READ] Sequence Vs Identity

Internals of Cache management
For a sequence object which is created with CACHE option, it will not pre-allocate the number of sequence numbers specified by the CACHE argument. Instead CACHE is maintained by two In-Memory variables one for tracking the Sequence Current Value another for the number of values left in the cache. So for sequence CACHE management the memory used will always be two instances of the data type of the Sequence object. For example, if a cache size of 50 is chosen, SQL Server does not keep 50 individual values cached. It only caches the current value and the number of values left in the cache. This means that the amount of memory required to store the cache is always two instances of the data type of the sequence object.

Following steps are performed by Sql Server whenever Next Value is requested from a Sequence object created with CACHING enabled option:

[ALSO READ] SEQUENCE Limitations and Restrictions

Let us understand this Sequence Cache Management Internals with an example:

Create the Sequence object SequenceExample with CACHE size as 50 as below:

CREATE SEQUENCE dbo.SequenceExample AS INT
    START WITH 1
    INCREMENT BY 1 
    CACHE 50;
GO

Let us request the first value from the Sequence object SequenceExample and verify that the last cached value 50 (because cache size is 50 and start value is 1 and increment value of the sequence is 1) is written to the system tables on the disk.

--Get First Sequence Value
SELECT (NEXT VALUE FOR dbo.SequenceEx) As NextSeqValue
--Verify the CACHE size 
SELECT * FROM SYS.SYSSCHOBJS 
WHERE ID = OBJECT_ID('dbo.SequenceEx')
--Verify the Last CACHED value written to the system table
SELECT * FROM SYS.SYSOBJVALUES 
WHERE OBJID = OBJECT_ID('dbo.SequenceEx')

From the above queries it is clear that when the first value is requested from the sequence, values 1 through 50 are made available from the two in-memory variables. The last cached value 50 is written to the system tables on the disk.

Now let us verify whether next sequence value 2 through 50 are made available from the two in-memory variables and no updates happen to the system table by using the below queries.

From the above queries results it is clear that no updates happened to the system tables when next sequence value is requested. This holds for all the next values till the sequence value 50.

By using the below queries read the sequence values from 3 through 50.

--Get sequence values from 3 to 50
SELECT (NEXT VALUE FOR dbo.SequenceEx) As NextSeqValue
GO 48

Now the request for the next sequence value i.e. 51 will cause the cache to be allocated again. The new last cached value 100 will be written to the system tables. Let us verify this using the below queries:

From the results of the above queries it is clear that the request for the sequence value 51 causes the cache to be allocated again. The new last cached value 100 is written to the system tables.

If the Database Engine is stopped after you use 55 numbers, the next intended sequence number in memory 56 is written to the system tables, replacing the previously stored number 100 before database engine is stopped.

After SQL Server restarts and a sequence number is needed, the starting number is read from the system table 56. The cache amount of 50 numbers (56-106) is allocated to memory and the new last cached value 106 is written to the system tables.

If the Database Engine stops abnormally say for example due to power failure (To simulate this you can go to task manager and kill the Sql Server process, please don’t try this on production database). As the last sequence value we have read is the 56, so next sequence value expected is 57 but instead of 57 it will return 107. Any sequence numbers allocated to memory (but never requested by a user or application) are lost. This functionality may leave gaps, but guarantees that the same value will never be issued two times for a single sequence object unless it is defined as CYCLE or is manually restarted.

[ALSO READ] :
SEQUENCE IN SQL SERVER 2012
SEQUENCE Limitations and Restrictions
Sequence Vs Identity
New Features in Sql Server 2012