In this article, we will examine the Identity Cache feature and the identity jump issue. Sometimes we see that identity jumps by some specific or random value in the auto-incremental columns, which is known as the identity jump issue. Usually, it occurs when the SQL Server instance is being forced to restart. This skipped gap is particularly depending on the data type of the column, and most of the time, it is possible that column type can be INT, BIGINT, or Numeric.
In the release of SQL Server 2012, Microsoft has introduced a feature of Identity Cache. The identity jump doesn’t cause any issue neither with the database nor the running tasks; however, this identity value gap is not acceptable in some of the business-oriented scenarios. This Identity counter values of the columns are stored in the system table separately, which is known as sys.identity_columns DMV. This reference object sys.identity_columns does not rely on the transaction status, and it doesn’t matter that the transaction on the user table is committed or rolled back. Now, in case this user transaction is being rolled back, we will find that the identity value has been skipped in the sys.identity_columns table. The reason being is that the value of the Identity column value does not get rolled back. Apart from the above scenario, there could be various reasons or situations where these issues of the skip of the Identity value will be seen, which will be small in number and random. However, when there is an issue of Identity value jump, the amount of the skip value will be prominent and precise in number, depending upon the column data type, which is easy to identify.
SQL Server is using a different cache size for the various data type of identity columns. The cache size of the INT data type column is 1000, and the Cache size of the BIGINT or Numeric data typed column is 10000. For example, we have a sample table named by sales.OrderTracking with identity column named OrderTrackingID and INT data type.
|
1 2 3 |
SELECT * FROM sales.OrderTracking ORDER BY OrderTrackingID DESC |
Here, the total number of records is 188790, and in the last inserted record’s identity value is 188790.
Verify the current identity value with the help of DMV (sys.identity_columns)
Below T-SQL statement returns the scope_identity for the table.
|
1 2 3 4 5 6 7 |
SELECT OBJECT_NAME(object_id) AS TableName, name AS ColumnName, TYPE_NAME(system_type_id) AS DataType, last_value FROM sys.identity_columns ic WHERE OBJECT_NAME(object_id) = 'OrderTracking' GO |
Inserting a new row in the table before force restart
|
1 2 3 |
INSERT INTO sales.OrderTracking(SalesOrderID, CarrierTrackingNumber, TrackingEventID, EventDetails, EventDateTime) VALUES(45681, '481CF8D-1C91-4ACC-9ABC-74', 6, 'Order has been delivered to its final destination', '2012-02-14 09:00:00.0000000') (1 row affected) |
With the insertion of one new row, the value of the identity will become 188791, and the next identity value should be 188792. Now, in case of a forceful reboot of the SQL Server Service with the help of Task Manager, the value of identity has jumped to a new value of 189790 for the column OrderTracking.
|
1 2 3 4 5 6 7 |
SELECT OBJECT_NAME(object_id) AS TableName, name AS ColumnName, TYPE_NAME(system_type_id) AS DataType, last_value FROM sys.identity_columns ic WHERE OBJECT_NAME(object_id) = 'OrderTracking' GO |
We can see here that the Identity counter has jumped by 999 for a current stat and with 1000 over the last transaction on the table. The identity jump value depends on the last insert operation over the table. For example, we have inserted ten rows in the last process then the identity jump will be 990 after the force restart of SQL Server.
Now, let us insert 1 row again in the same table and verify the newly added row in the table.
|
1 2 3 |
INSERT INTO sales.OrderTracking(SalesOrderID, CarrierTrackingNumber, TrackingEventID, EventDetails, EventDateTime) VALUES(45681, '481CF8D-1C91-4ACC-9ABC-74', 6, 'Order has been delivered to its final destination', '2012-02-14 09:00:00.0000000') (1 row affected) |
In here, we can see that the new row inserted has got the identity value as 189791.
Before restarting the SQL Server instance, consecutive values of identity was being generated without any significant interruption. However, after the SQL Server service was restarted, we can see a gap in the incrementing Identity value, which indicates SQL Server is caching the value of identity columns and reseeding the column after the process of force restart takes place. To avoid this circumstance, we can choose any one of below three choices:
- Disable the IDENTITY CACHE option at the database level
- Disable the IDENTITY CACHE at the server level with the help of Trace Flag
- Use a sequence with the NOCACHE option in replacement of the Identity option with the column
Disable the IDENTITY CACHE option at the database level
The default configuration value of IDENTITY CACHE will be set as ‘ON’ for each user database level in SQL Server. Fundamentally IDENTITY CACHE is a feature to boost the performance of the insert operation. However, it could be disabled at the database level to avoid the identity gap in the column of the tables.
|
1 2 3 4 |
USE DatabaseName GO ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE = OFF GO |
Disable the IDENTITY CACHE at SQL Server instance level
IDENTITY CACHE can be disabled at SQL Server instance level with the help of ‘Trace flag 272’. We can add this TRACE flag in the start-up parameter to avoid executing it manually at the time of the start-up of the SQL Server instance.
Open the property of SQL Server Services in SQL Server Configuration Manager > Properties > Startup Parameters > Add as shown below.
Adding a TRACE FLAG in Startup Parameters
Here, we are adding a ‘TRACE flag 272’ in SQL Server instance property using -T272 in the start-up parameter. SQL Server service needs to be restarted for changes to take place in the start-up parameter.
Restart SQL Server Instance service to apply the changes
The steps mentioned above will disable the IDENTITY CACHE at the server level for each existing database as well as the database being created. After applying the changes in the start-up parameter, users will be asked with the below prompt to restart the SQL Server instance.
SEQUENCE with NOCACHE option
SEQUENCE is a user-defined incrementing object in the SQL Server database, which produces an integer incremental identity or sequence, according to the schema definition and specification. A SEQUENCE can be used as an artificially incremented Identity property for a column of the table. It works as a sequence generator, which was introduced in SQL Server 2012. We can use a sequence with the NOCACHE option with the primary key as a substitution of IDENTITY() to avoid the identity jump issue with the primary key.
Creating a SEQUENCE with NOCACHE
|
1 2 3 4 5 6 7 |
CREATE SEQUENCE Seq_OrderTracking AS BIGINT START WITH 1 INCREMENT BY 1 MINVALUE 0 NO MAXVALUE NO CACHE |
Here, we have a sequence to generate a unique and incremental value generation option that starts from 1 and undefined maximum value. If users want to use a sequence with the table, then the table should be specified without the IDENTITY() parameter on the column, and the sequence object will be used in the INSERT statement when inserting a new row into the table.
For example:
|
1 2 |
INSERT INTO sales.OrderTracking(OrderTrackingID, SalesOrderID, CarrierTrackingNumber, TrackingEventID, EventDetails, EventDateTime) VALUES(NEXT VALUE FOR Seq_OrderTracking, 45681, '481CF8D-1C91-4ACC-9ABC-74', 6, 'Order has been delivered to its final destination', '2012-02-14 09:00:00.0000000') |
NEXT VALUE FOR sequence command increments the counter and generates a new value for the object. Users can perform the counter reseed and kind of commands with the sequence using different sequence commands.
This SEQUENCE solution resolves your identity jump issue with the identity columns in the SQL Server. However, you can approach this when the user is designing the database for the new product. For the existing system, this approach is not recommended because of many dependent object changes required to be done.
Conclusion
Usually, the identity column is being used as the dark column, and that can be used to refer to the other tables (Primary – Foreign Key reference). There are numerous reasons to see the gap in the incremental sequence. It is not a matter of worry unless significant differences are found in the identity columns repetitively repetitive or randomly. When a big identity gap is found in the identity column, the first action that should be taken is to check the IDENTITY CACHE option for that particular database before proceeding with any further investigation.
To resolve this issue for future databases, disabling the IDENTITY CACHE at the server level is recommended by adding the ‘Trace flag’ in the start-up parameter of the SQL Server Service. Ideally, the database administrator will have the number of Pre and Post activity list to perform on the new SQL Server setup. Fundamentally this ‘Trace flag’ should be included in the database administrators checklist for the Server Migration or Tech-Refresh kind of activities when the users won’t IDENTITY CACHE feature.
View all posts by Jignesh Raiyani
- Page Life Expectancy (PLE) in SQL Server - July 17, 2020
- How to automate Table Partitioning in SQL Server - July 7, 2020
- Configuring SQL Server Always On Availability Groups on AWS EC2 - July 6, 2020