Have you ever experienced a sudden jump an identity column in SQL Server? Yeah, SQL Server caches the identity values and if you have a hard shutdown, you have consumed a number of values from that cache.
Should you care about the gap? In most cases: no. The identity value should be meaningless. In many cases I think that it is just an aesthetic issue to not have these gaps. (I’ve seen cases where you do run into problems because of the gap, I should add – but not frequently.)
For the SEQUENCE object, we have the CACHE option to specify how many values to cache. I.e., max values we can jump if we have a hard shutdown.
For identity, we have the IDENTITY CACHE database scoped configuration, introduced in SQL Server 2017. Caching on or off. On is default. We also have trace flag 272, at the instance level.
However, disabling the caching isn’t free. I’ve seen numerous posts on disabling the cache and not getting gaps. But I didn’t find any elaboration on performance. Hence this article. If you’ve done performance testing for this or know about such article, please leave a comment!
The problem is as always how to set up a realistic test case. What would the typical table structure and insert pattern look like? How long is a piece of string? So I decided to skip that part and go for the most simplified case I could:
A table with an identity columns and one more int column. (The reason for that second column is to hopefully avoid any special handling for identity-only tables in SQL Server.) The insert is an insert with a subselect. I.e., all rows in one transaction.
You know this already: your mileage will vary!
Anyhow, for my test, I consistently had about 5 times better performance with identity caching turned on. IMO, this is at least enough to show that caching identity values *can* be have performance aspect to; not turn it off “just because”.
--Test performance penalty for lowering identity cache
SET NOCOUNT ON
USE master
DROP DATABASE IF EXISTS Identity_test
GO
CREATE DATABASE Identity_test
ON PRIMARY
( NAME = N'Identity_test', FILENAME = N'C:\DemoDatabases\DbFiles\a\Identity_test.mdf' , SIZE = 500MB , MAXSIZE = UNLIMITED, FILEGROWTH = 64MB )
LOG ON
( NAME = N'Identity_test_log', FILENAME = N'C:\DemoDatabases\DbFiles\a\Identity_test_log.ldf' , SIZE = 500MB , MAXSIZE = 2048GB , FILEGROWTH = 64MB )
GO
USE Identity_test
DROP TABLE IF EXISTS t
--Our identity table
CREATE TABLE t(c1 int IDENTITY(1,1), c2 int)
--Where we will log execution times
CREATE TABLE timing(run int, descr varchar(50), ms int)
ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE = ON
GO
WAITFOR DELAY '00:00:01'
GO
DECLARE @a time(7) = SYSDATETIME()
INSERT INTO t
SELECT TOP(1000000) 1 FROM sys.columns AS a CROSS JOIN sys.columns AS b
INSERT INTO timing (run, descr, ms)
VALUES(1, 'Cache on', DATEDIFF(ms, @a, CAST(SYSDATETIME() AS time(7))))
TRUNCATE TABLE t
GO
WAITFOR DELAY '00:00:01'
GO
ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE = OFF
GO
WAITFOR DELAY '00:00:01'
GO
DECLARE @a time(7) = SYSDATETIME()
INSERT INTO t
SELECT TOP(1000000) 1 FROM sys.columns AS a CROSS JOIN sys.columns AS b
INSERT INTO timing (run, descr, ms)
VALUES(2, 'Cache off', DATEDIFF(ms, @a, CAST(SYSDATETIME() AS time(7))))
TRUNCATE TABLE t
GO
ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE = ON
GO
WAITFOR DELAY '00:00:01'
GO
DECLARE @a time(7) = SYSDATETIME()
INSERT INTO t
SELECT TOP(1000000) 1 FROM sys.columns AS a CROSS JOIN sys.columns AS b
INSERT INTO timing (run, descr, ms)
VALUES(3, 'Cache on', DATEDIFF(ms, @a, CAST(SYSDATETIME() AS time(7))))
TRUNCATE TABLE t
GO
WAITFOR DELAY '00:00:01'
GO
ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE = OFF
GO
WAITFOR DELAY '00:00:01'
GO
DECLARE @a time(7) = SYSDATETIME()
INSERT INTO t
SELECT TOP(1000000) 1 FROM sys.columns AS a CROSS JOIN sys.columns AS b
INSERT INTO timing (run, descr, ms)
VALUES(4, 'Cache off', DATEDIFF(ms, @a, CAST(SYSDATETIME() AS time(7))))
TRUNCATE TABLE t
GO
ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE = ON
GO
WAITFOR DELAY '00:00:01'
GO
DECLARE @a time(7) = SYSDATETIME()
INSERT INTO t
SELECT TOP(1000000) 1 FROM sys.columns AS a CROSS JOIN sys.columns AS b
INSERT INTO timing (run, descr, ms)
VALUES(5, 'Cache on', DATEDIFF(ms, @a, CAST(SYSDATETIME() AS time(7))))
TRUNCATE TABLE t
GO
WAITFOR DELAY '00:00:01'
GO
ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE = OFF
GO
WAITFOR DELAY '00:00:01'
GO
DECLARE @a time(7) = SYSDATETIME()
INSERT INTO t
SELECT TOP(1000000) 1 FROM sys.columns AS a CROSS JOIN sys.columns AS b
INSERT INTO timing (run, descr, ms)
VALUES(6, 'Cache off', DATEDIFF(ms, @a, CAST(SYSDATETIME() AS time(7))))
--TRUNCATE TABLE t
GO
--Result?
SELECT * FROM timing ORDER BY run
SELECT
AVG(CASE descr WHEN 'cache on' THEN ms ELSE NULL END) AS cache_on
,AVG(CASE descr WHEN 'cache off' THEN ms ELSE NULL END) AS cache_off
,CAST(
CAST(AVG(CASE descr WHEN 'cache off' THEN ms ELSE NULL END) AS decimal(9,2)) /
AVG(CASE descr WHEN 'cache on' THEN ms ELSE NULL END)
AS decimal(9,2)) AS ratio
FROM timing