Is there any problem if we disable the IDENTITY CACHE at SQL Server instance level

Question

In SQL Server an identity column can jump by 10000 if we restart the service. To resolve this problem I disable the IDENTITY CACHE at SQL Server instance level.

But is there in disadvantage of this?

Answer 1

IDENTITY CACHE is a database level scoped configuration. You can disable it using below command.

ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE = OFF

It is ON by default. If you disable it, there will be impact to INSERT performance. The reference documentation says:

IDENTITY_CACHE = { ON | OFF }

APPLIES TO: SQL Server (Starting with SQL Server 2017 (14.x)), Azure SQL Database and Azure SQL Managed Instance

Enables or disables identity cache at the database level. The default is ON. Identity caching is used to improve INSERT performance on tables with identity columns. To avoid gaps in the values of an identity column in cases where the server restarts unexpectedly or fails over to a secondary server, disable the IDENTITY_CACHE option. This option is similar to the existing Trace Flag 272, except that it can be set at the database level rather than only at the server level.

Note:

• If you want to disable at the instance level, you have to use trace flag 272.
• Even without the cache option, there can be gaps in identity values, due to many scenarios. Refer to SO Post