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?
Best Answer
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:
Result:
To dig a bit deeper I used the extended events
sqlserver.metadata_persist_last_value_for_sequence
andsqlserver.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.
There is no difference in output for using no cache and cache of 1.
Sample output:
When using a cache of 4.
The
SCH_S
lock is done when a value is needed. And when the cache is exhausted it is followed by aIX
and the aU
lock and finally the eventmetadata_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.
So, SQL Server thinks there is no difference and tells me so. There is however a difference in
sys.sequences
in the columncache_size
. It is NULL for no cache and 1 for a cache of 1.