SQL SERVER – Identity Jumping 1000 – IDENTITY_CACHE

SQL Server 2017 has a wonderful feature which fixes a very old issue which DBA and developers have been struggling for a while. It would be impossible to find DBA or Developer who has not faced an issue when their identity column value has jumped or incremented by 1000. It is a very common issue and today we will see the solution of this issue with the help of IDENTITY_CACHE introduced in the latest version of SQL Server.

First, let us understand the issue which we face when we keep IDENTITY_CACHE = ON for SQL Server. Please note that as this is a default value is ON, so if you do not change anything you will see the following behavior.SQL SERVER - Identity Jumping 1000 - IDENTITY_CACHE identitycache-800x336

Case 1: IDENTITY_CACHE = ON — default

First, make sure that value of identity cache is set to on by running following command.

ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE = ON
GO

Create a sample table where there is one column which is identity.

IF OBJECT_ID('dbo.Identity_Cache_Demo', 'U') IS NOT NULL
DROP TABLE dbo.Identity_Cache_Demo;
GO
CREATE TABLE Identity_Cache_Demo
(ID INT IDENTITY(1,1) PRIMARY KEY,
Colors varchar(50));
GO

Now let us insert few rows into the table and right after that let us insert few rows into the table.

INSERT INTO Identity_Cache_Demo (Colors) VALUES ('Red');
INSERT INTO Identity_Cache_Demo (Colors) VALUES ('White');
GO

Next, let us check the values in the table by selecting the data.

SELECT *
FROM Identity_Cache_Demo
GO

You may notice that the value of the ID column is 1 and 2.

Solarwinds

SQL SERVER - Identity Jumping 1000 - IDENTITY_CACHE identitycache1

Now we will run the following command inside a transaction. Please note that we will just begin the transaction but we will not complete the transaction.

BEGIN TRANSACTION
INSERT INTO Identity_Cache_Demo (Colors) VALUES ('Brown');
INSERT INTO Identity_Cache_Demo (Colors) VALUES ('Pink');

Now we reached to a very important stage. We need to create a crash for SQL Server. To recreate unexpected shutdown, go to a New Query Window and run following command.

-- Run only in new query window
SHUTDOWN WITH NOWAIT -- New Window
GO

Now connect to SQL Server once again by turning on the services.

If you run the select command once again, you will not see the data which we had initiated in the transaction because the transaction was never complete and the data was never committed. This is correct behavior.

SELECT *
FROM Identity_Cache_Demo
GO

Now let us insert a couple of more rows into the table.

INSERT INTO Identity_Cache_Demo (Colors) VALUES ('Blue');
INSERT INTO Identity_Cache_Demo (Colors) VALUES ('Orange');
GO

Once again we will run the select command see what is the value of the ID column.

SELECT *
FROM Identity_Cache_Demo
GO

SQL SERVER - Identity Jumping 1000 - IDENTITY_CACHE identitycache2

You will notice that there is a gap of 1000 value in the Identity. This is because SQL Server had generated and cached the next 1000 identity values for the table. SQL Server does this for the improve the performance of the INSERT statements which runs on the table. However, due to the unexpected crash scenario, it also loses the pre-generated identity value and that behavior is the responsible for the 1000 value jump in the identity.

If you want to overcome this issue, you have to follow the CASE 2 where we keep the identity cache value to OFF.

Case 2: IDENTITY_CACHE = OFF

In this scenario, we will run the following script to turn off the identity cache.

-- Case 2 IDENTITY_CACHE = OFF
ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE = OFF
GO

Once we do this, we will run exactly the same scenario we ran in case 1 once again. As I have explained all the steps in detail in Case 1, I will directly jump the scenario where we insert values after the unexpected shutdown.

Please repeat all the steps which you have followed in step 1 and after once again run the SELECT statement. Over here you will notice that this time, it does not jump or miss any identity value.

SELECT *
FROM Identity_Cache_Demo
GO[/sql

SQL SERVER - Identity Jumping 1000 - IDENTITY_CACHE identitycache3

However, when you keep your identity cache value to OFF, your SQL Server takes a very little performance hit, which you can safely ignore if you do not see any CPU or Memory Issues.

This feature can be very important when you are dealing with replication with heterogeneous databases. If you are doing replication between multiple database platforms, I suggest that you take a look at SharePlex. With the help of SharePlex, you can achieve high availability, scalability and data integration through real-time database replication across on-premises, cloud or hybrid databases.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
, , ,
Previous Post
SQL SERVER – Performance Observation of TRIM Function
Next Post
SQL SERVER – Improve Index Rebuild Performance by Enabling Sort In TempDB

Related Posts

11 Comments. Leave new

  • Laércio Coelho
    January 24, 2018 4:35 pm

    Excelent explanation, Pinal.
    But, how do I know if de IDENTITY_CACHE is ON or OFF at this moment?

    Thanks!

    Reply
  • Hi Pinal,

    Can you write a blog on how to audit database growth size over period of time.
    Or if you written already please point me to there, I could not find.

    Reply
  • Any idea why it’s not working on the (localhost)ProjectsV13 SQL Express database?

    Reply
  • Thank you for this article. I was really confused as to why the identity column had jumped.

    Can I ask if it is safe to just turn off the identity cache in a live environment?
    For example, if a live database table has an identity column that has jumped rows by 1000/10000 on a number of occasions due to unexpected restart/shutdowns and we just turn off the identity cache on sql server, will this cause any issues or will the identity column simply increment from the last as normal from there on?

    Reply
  • Hi, I came across your article because we experienced the same problem, but it was with SQL 2016. There doesn’t seem to be any acknowledgement that this is a problem with 2016, but our SQL Server is definitely 2016 and we had the identity gap problem occur.

    Reply
  • Suresh Kamrushi
    February 26, 2020 1:12 pm

    Nice article. Great explanation. Please keep writing.

    Reply
  • Muhammad Usman
    March 13, 2020 11:00 am

    Hi,

    Nice explanation as always. Keep it up.

    I will like to discuss a point, i have encountered jumps in a single table:

    Last Id was 2713, next was 12713, so 1000 jump seen.

    Later on Id was 12720, next was 22720, 10,000 jump seen.

    If the problem occurs again then we might have a 1,00,000 jump?

    It seems that cache is not 1000 identities. This can be a real big problem over time when int limit is reached for identity columns.

    Need your expert opinion on how to tackle this case. We are using SQL 2017 with replication.

    What happens when Identity limit is reached, what are next steps for it?

    Regards,

    Muhammad Usman

    Reply
  • spent long time to find the solution……….

    Reply

Leave a Reply

Menu