A missing sequence number after a restart can alarm a finance team even when no row was deleted. Sequence cache size trades fewer metadata writes for possible unused values after an unexpected stop.
Understand Where Gaps Come From
SQL Server can cache sequence values so it does not persist each issued value individually. An unexpected shutdown can discard unused cached values, leaving a jump when the next cache is allocated. NO CACHE reduces that restart-related gap, but it still does not promise gap-free numbers. A transaction that obtains a value and rolls back has consumed it. A failed insert can do the same. I explain this before changing settings because the business requirement, not the default, decides the design.
If invoice numbers must be strictly gap-free under a legal process, a general SEQUENCE is usually the wrong mechanism by itself. Design a controlled issuance and audit process with the finance owner.
Inspect the Current SEQUENCE Cache Size
sys.sequences reports whether caching is enabled, configured cache size, current value, and other limits. Review cycling and minimum and maximum values as well. A sequence shared by several tables has a different usage pattern from one tied to a single insert path. I check callers before changing cache. A sudden gap can come from rollback or a caller that requests numbers in advance, not only from a restart.
SELECT SCHEMA_NAME(schema_id) AS schema_name, name, current_value,
is_cached, cache_size, is_cycling, minimum_value, maximum_value
FROM sys.sequences
ORDER BY schema_name, name;Test Three SEQUENCE Cache Size Settings
Create three sequences in an isolated test database with the same data type and start value, changing only CACHE 50, CACHE 1000, or NO CACHE. Execute the same insert workload against each one. Record elapsed time, CPU, log activity, and rows inserted from actual test runs. Reset the test tables and control concurrency between cases. Do not paste a made-up performance gain into the report. I also repeat the test because the first run can include compilation and file growth.
CREATE SEQUENCE dbo.SeqCache50 AS bigint START WITH 1 INCREMENT BY 1 CACHE 50;
CREATE SEQUENCE dbo.SeqCache1000 AS bigint START WITH 1 INCREMENT BY 1 CACHE 1000;
CREATE SEQUENCE dbo.SeqNoCache AS bigint START WITH 1 INCREMENT BY 1 NO CACHE;
SELECT NEXT VALUE FOR dbo.SeqCache50 AS sample_value;Measure Insert Throughput Honestly
Use the same table definition, indexes, batch size, and client concurrency for each case. Capture start and end time around a fixed number of inserts, then divide completed rows by measured seconds. Run long enough that setup noise does not dominate. A loop in SSMS is a simple demonstration, but it is not a substitute for the application's concurrent insert pattern. I check waits and log throughput too. A larger cache can help sequence allocation while another bottleneck still limits inserts.
What happens during a failover or restart in the actual environment? Test that separately on a disposable instance. The throughput benchmark and gap test answer different questions and should be reported separately. SET NOCOUNT ON at the top of the loop script keeps SSMS from printing a row count for every insert.
SET NOCOUNT ON;
CREATE TABLE #SequenceInsert (id bigint NOT NULL);
DECLARE @i int = 0, @started datetime2(7) = SYSUTCDATETIME();
WHILE @i < 10000
BEGIN
INSERT #SequenceInsert(id) VALUES (NEXT VALUE FOR dbo.SeqCache50);
SET @i += 1;
END;
SELECT COUNT(*) AS inserted_rows,
DATEDIFF_BIG(microsecond, @started, SYSUTCDATETIME()) AS cache50_elapsed_us
FROM #SequenceInsert;
TRUNCATE TABLE #SequenceInsert;
SET @i = 0; SET @started = SYSUTCDATETIME();
WHILE @i < 10000
BEGIN
INSERT #SequenceInsert(id) VALUES (NEXT VALUE FOR dbo.SeqCache1000);
SET @i += 1;
END;
SELECT COUNT(*) AS inserted_rows,
DATEDIFF_BIG(microsecond, @started, SYSUTCDATETIME()) AS cache1000_elapsed_us
FROM #SequenceInsert;
TRUNCATE TABLE #SequenceInsert;
SET @i = 0; SET @started = SYSUTCDATETIME();
WHILE @i < 10000
BEGIN
INSERT #SequenceInsert(id) VALUES (NEXT VALUE FOR dbo.SeqNoCache);
SET @i += 1;
END;
SELECT COUNT(*) AS inserted_rows,
DATEDIFF_BIG(microsecond, @started, SYSUTCDATETIME()) AS no_cache_elapsed_us
FROM #SequenceInsert;
Do Not Promise NO CACHE Is Gapless
NO CACHE persists sequence state more frequently, so it avoids losing a whole unused cache on an unexpected stop. It does not put an issued value back when a transaction rolls back. Show this in a test: request a value inside a transaction, roll back, then request another. The first number is still spent. That behavior is fundamental to sequences being generated outside transaction rollback. I use that small test when someone asks for "no gaps" and offers NO CACHE as the complete answer.
BEGIN TRANSACTION;
SELECT NEXT VALUE FOR dbo.SeqNoCache AS rolled_back_value;
ROLLBACK TRANSACTION;
SELECT NEXT VALUE FOR dbo.SeqNoCache AS next_value;Choose an Invoice Number Design Separately
An invoice number can carry legal and audit expectations that a surrogate key does not. If every issued number must be accounted for, design a posting process that records reserved, voided, and finalized numbers with reasons. Serialize only the critical issuance step. Keep the invoice row and audit entry in one controlled transaction, then define what a canceled invoice means. I do not promise that NO CACHE alone satisfies this rule. Rollbacks and failed inserts still consume sequence values, and manual requests can create gaps with no row.
Ask the finance owner whether gaps are forbidden or whether documented voids are acceptable. Those are different designs. A gap-free promise can reduce insert concurrency and make recovery more complex. State the tradeoff in plain terms before selecting a cache setting.
Read SEQUENCE Cache Size Metadata Carefully
The current_value in sys.sequences reflects obligated sequence state, not a simple count of committed rows. Compare it with the number of inserted rows only to understand how values were requested, never to prove data loss. A process can request values in advance and use only some. A rollback can consume a value without a row. A restart can discard a cached remainder. I test each path in a disposable database and document the observed behavior from the test, rather than writing a theoretical gap size into the article.
For throughput testing, compare completed inserts per measured second across the three configurations and repeat under concurrency. Record log activity and waits as well. NO CACHE adds metadata persistence work, but another bottleneck can hide that cost. CACHE 1000 can help a hot allocator while increasing the potential unused range after a crash. The decision should combine actual throughput evidence with the identifier's business contract.
Test sequence exhaustion and cycling rules too. A cached sequence with a small maximum can fail independently of restart gaps. I check data type, minimum, maximum, and whether CYCLE is enabled before changing cache. The application should treat a duplicate or exhausted identifier as an error, not silently continue. Cache tuning does not replace capacity planning for the key space.
Choose a Rule That Fits the Identifier
For surrogate keys, gaps usually carry no business meaning. Choose a cache that supports throughput and monitor exhaustion or cycling rules. For externally visible numbers, document whether gaps are acceptable and how they are explained. If every number must be accounted for, reserve and finalize numbers in a serialized, audited workflow. That can reduce throughput, which is a business tradeoff to approve explicitly. I keep the sequence cache choice in the schema review because changing it later can surprise reports that incorrectly interpret gaps as missing rows.
Compare the measured test result with the business rule. A fast sequence that violates an invoice policy is wrong. A gap-free design imposed on an internal identity can be expensive theater.
Related reading on this blog: Identity Jumping 1000: IDENTITY_CACHE and Find Missing Identity Values.
A sequence value is not a row count, it is an identifier issued under a contract.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
