Share via

How can I prevent crash from IDENTITY column value jump

  • Article

Introduction

This article presents a common change in values ​​of columns that have the IDENTITY property, which is change in your next consecutive value. This generates "missing" gaps in numerical sequence.

The change in these values, by default ​​occurs whenever restarted SQL Server instance service and multiple INSERT statements without data transactions. Usually generates inconvenience to users who are unaware CACHE performed by SQL Server to optimize performance of these columns.

The problem is simulated and also presenting a model to set parameter in SQL Server and keep the columns with IDENTITY property when filling consecutive values​​.

Building the Environment for Testing

So to demonstrate how the default behavior in a table that has an IDENTITY column (using CACHE on SQL Server Instance) and show how to change this to always get the last value assigned on table (NOCACHE), you must create a table with some data to help us understand how this happens.

So this demo table (Image 1) has only two columns: ID_CLIENT (with "smallint" datatype and the "IDENTITY" property) and NM_CLIENT (with "varchar" datatype).

The NM_CLIENT column is filled sequentially to facilitate data identification.

Image 1 - Creating DEMO table and adding simulation data

See below this T-SQL script

 CREATE TABLE TB_TEST (
 ID_CLIENT  smallint IDENTITY (1, 1) NOT NULL,
 NM_CLIENT  varchar(20) NOT NULL
)
GO

INSERT INTO TB_TEST VALUES ('CLIENT 1');
INSERT INTO TB_TEST VALUES ('CLIENT 2');
INSERT INTO TB_TEST VALUES ('CLIENT 3');
GO

SELECT * FROM TB_TEST;
GO

It's possible to check if data are being correctly inserted, you can have performed a simple query on table. In this case, you can see that data were filled consecutively into ID_CLIENT column using CACHE of IDENTITY property linked it.

Causing Gaps in a Sequence Number

See the default behavior of SQL Server instance, using CACHE to data store of each column with IDENTITY property is required generate an interrupt access SQL Server instance, restarting related to SQL Server service (Image 2) and causing gaps until last value that each column with the IDENTITY property features. 

Now that CACHE server for IDENTITY columns in SQL instance was disposed, SQL Server will store a new value in CACHE so that a new row is added, so the SQL Server can guarantee the "uniqueness" value for this column.

Image 2 - Restarting the SQL Server service

Let's add more 3 rows to identify how the SQL instance will use IDENTITY columns without CACHE defined value.

Including data in same way as would be performed before SQL instance restarted (Image 3).

Image 3 - Inserting rows after restarting the SQL Server service

See below this T-SQL script

INSERT INTO TB_TEST VALUES ('CLIENT 4');
INSERT INTO TB_TEST VALUES ('CLIENT 5');
INSERT INTO TB_TEST VALUES ('CLIENT 6');
GO

SELECT * FROM TB_TEST;
GO

You can see that the SQL Server jumped a value to add next row indicated in IDENTITY column and this has resulted (in this demo) in a 99 gap missing rows. These rows were never included or excluded, but interval between rows with ID_CLIENT = 3 and ID_CLIENT = 102 can generate doubts for users and raise questions about what may have actually happened to "jump" 3 to 102 values.

If restarted the SQL Server service again and use DBCC CHECKIDENT command just to check what is current value of IDENTITY column (Image 4). You can see one more "jump" value, ​​now leaving 104 for 204 values. As indicated earlier, this value could have "jumped" to any value because there is no default defined to add data in this structure using CACHE IDENTITY property.

Image 4 - Displaying the current value of the IDENTITY column on table

See below this T-SQL script

DBCC CHECKIDENT(TB_TEST, RESEED)
GO

All this change values​​, jumping in intervals without a default defined may generate doubt:
"This is an IDENTITY property runtime error ?"

The SQL Server development team replied through Microsoft Connect:
This behavior is actually by design as we try to guarantee uniqueness of the ID rather than making sure we don't have gaps. As a result, we do skip some values just in case in certain scenarios so that we don't have any issues around accidentally repeating numbers.

This reply It's possible conclude that "jump" values ​​is not problem related to IDENTITY property, but in fact is expected behavior to maintain data adding in columns that use this property.

Changing the startup SQL Server service

Although knowing that this is expected default behavior for SQL Server, in some cases don't possible allow this.

So can change SQL Server startup service to don't use CACHE in columns with IDENTITY property to keep their last values ​​stored.

This frees up space in memory consumed by SQL Server, but requires that whenever table be referred to check which was last stored value in the column.

 Warning
The big data tables might be impacted on performance. In this case, It's recommended to create a separate table containing current value of your spine and manage access to table and assigning their numbers with your application.

For more information read IDENTITY property.

So to use IDENTITY property with NOCACHE condition is need to add parameter to SQL Server startup service through SQL Server Configuration Manager tool (Image 5).

You can access Properties window, just click with right mouse button in this service and select "Properties" option, then a window will appear with few tabs related to Service settings. Select "Startup Parameter" tab and fill in textbox the "-t272" value (the letter "t" must be lowercase for this Trace Flag is enabled), referring to TRACE 272 and click "Add" button.

**Image 5 - Changing **SQL Server startup service on Configuration Manager (click to enlarge)

After you confirm changes, click on "Ok" button.

Therefore this parameter is always loaded at startup of SQL Server service.

Using TRACE 272

Now that SQL Server Instance service is configured to always be initialized with TRACE 272 parameter, then I restart service and redo this demo from beginning to verify that IDENTITY property should take time to NOCACHE with this condition.

For this new demo, run the steps below (Image 6):

  • Step 1 - Clearing table;
  • ****Step 2 - Inserting the first data;
  • ****Step 3 - Restarting the SQL Server service (forcing the CACHE empty);
  • **Step ** 4 - Inserting more data and querying table contents;

Image 6 - Remaking demo after enabling TRACE 272

See below this T-SQL script

--STEP 1 (NEW TEST, CLEAR TABLE)
TRUNCATE TABLE TB_TEST;
GO

--STEP 2 (ADD ROWS AGAIN)
INSERT INTO TB_TEST VALUES ('CLIENT 1');
INSERT INTO TB_TEST VALUES ('CLIENT 2');
INSERT INTO TB_TEST VALUES ('CLIENT 3');
GO

--STEP 3 (RESTART SQL INSTANCE SERVICE - use "services.msc")

--STEP 4 (ADD NEW ROWS)
INSERT INTO TB_TEST VALUES ('CLIENT 4');
INSERT INTO TB_TEST VALUES ('CLIENT 5');
INSERT INTO TB_TEST VALUES ('CLIENT 6');
GO

SELECT * FROM TB_TEST;
GO

It's possible to verify in the SELECT statement, that ID_CLIENT column has IDENTITY property to add rows consecutive values and ​​maintained the number sequence without jumping their values ​​after SQL Server service restarting in "Step 3".

Conclusion

Although these jump are not seen as a problem, this "jump" values ​​may cause doubts on consistency processes, so It's essential to know the needs of your application and then start the settings for your SQL Server instance and subsequently to their Databases.

Using TRACE 272 as a solution to apply NOCACHE condition is a best practice for most of these cases, but analyze tables and make check performance in an Test Environment before applying this change in your Database Production.

References

See Also

Other Languages