Sequence - NO CACHE vs CACHE 1

Question

Is there any difference between a SEQUENCE declared using NO CACHE and one declared using CACHE 1 in SQL Server 2012+?

Sequence #1:

CREATE SEQUENCE dbo.MySeqCache1
AS INT
    START WITH 1
    INCREMENT BY 1
    MINVALUE 1
    MAXVALUE 9999
    NO CYCLE
    CACHE 1;
GO

Sequence #2:

CREATE SEQUENCE dbo.MySeqNoCache
AS INT
    START WITH 1
    INCREMENT BY 1
    MINVALUE 1
    MAXVALUE 9999
    NO CYCLE
    NO CACHE;
GO

Is there any difference between the two? Will they behave differently when being used in a SQL Server 2012+ environment?

Answer 1

It is hard to give a definitive answer on this question until you actually find a difference. I have found none but that does not mean there is no difference only that I have not seen one in the tests I have made.

The easy test is for performance. Either getting the next value in a loop or using a numbers table as a source to generate multiple values at a time. In my tests there were no difference in performance between using no cache and a cache of 1 value but there was a significant performance improvement of using a cache of 2.

This is the code I used to test performance:

declare @D datetime = getdate();

declare @I int = 0;
while @I < 9999
  select @I = next value for dbo.S;

select datediff(millisecond, @D, getdate());

Result:

Cache        Time(ms)
------------ --------
NO CACHE     1200
1            1200
2             600
1000           70  

To dig a bit deeper I used the extended events sqlserver.metadata_persist_last_value_for_sequence and sqlserver.lock_acquired to see if there was something different in how the values persists to the system table.

I used this code to test for no cache and cache size of 1 and 4.

DECLARE @S NVARCHAR(max) = '
CREATE EVENT SESSION SeqCache ON SERVER 
ADD EVENT sqlserver.lock_acquired(
    WHERE (sqlserver.session_id=({SESSIONID}))),
ADD EVENT sqlserver.metadata_persist_last_value_for_sequence(
    WHERE (sqlserver.session_id=({SESSIONID}))) 
ADD TARGET package0.event_file(SET filename=N''d:\SeqCache'');';

SET @S = REPLACE(@S, '{SESSIONID}', CAST(@@SPID AS NVARCHAR(max)));

EXEC (@S);

GO

CREATE SEQUENCE dbo.S
AS INT
    START WITH 1
    INCREMENT BY 1
    MINVALUE 1
    MAXVALUE 9999
    NO CYCLE
    NO CACHE;
--    CACHE 1;
--    CACHE 4;

GO

ALTER EVENT SESSION SeqCache ON SERVER STATE = START;

GO

DECLARE @I INT = 0;
WHILE @I < 10
  SELECT @I = NEXT VALUE FOR dbo.S;

GO

ALTER EVENT SESSION SeqCache ON SERVER STATE = STOP;
DROP EVENT SESSION SeqCache ON SERVER;
DROP SEQUENCE dbo.S;

There is no difference in output for using no cache and cache of 1.

Sample output:

name                                      persisted_value mode
----------------------------------------- --------------- -----
lock_acquired                             NULL            SCH_S
lock_acquired                             NULL            IX
lock_acquired                             NULL            U
metadata_persist_last_value_for_sequence  1               NULL
lock_acquired                             NULL            SCH_S
lock_acquired                             NULL            IX
lock_acquired                             NULL            U
metadata_persist_last_value_for_sequence  2               NULL
lock_acquired                             NULL            SCH_S
lock_acquired                             NULL            IX
lock_acquired                             NULL            U
metadata_persist_last_value_for_sequence  3               NULL

When using a cache of 4.

name                                      persisted_value mode
----------------------------------------- --------------- -----
lock_acquired                             NULL            SCH_S
lock_acquired                             NULL            IX
lock_acquired                             NULL            U
metadata_persist_last_value_for_sequence  4               NULL
lock_acquired                             NULL            SCH_S
lock_acquired                             NULL            SCH_S
lock_acquired                             NULL            SCH_S
lock_acquired                             NULL            SCH_S
lock_acquired                             NULL            IX
lock_acquired                             NULL            U
metadata_persist_last_value_for_sequence  8               NULL

The SCH_S lock is done when a value is needed. And when the cache is exhausted it is followed by a IX and the a U lock and finally the event metadata_persist_last_value_for_sequence is fired.

So there should be no difference between using no cache and and cache 1 when it comes to potentially losing values on an unexpected shutdown of SQL Server.

Finally I noticed something in the Message tab in SSMS when creating a sequence with cache 1.

The cache size for sequence object 'dbo.S' has been set to NO CACHE.

So, SQL Server thinks there is no difference and tells me so. There is however a difference in sys.sequences in the column cache_size. It is NULL for no cache and 1 for a cache of 1.