Background: What is the identity burning set factor?
The identity burning set factor configuration parameter is a percentage in decimal form x 10 million.
The default value, 5000, represents .05%, or .0005.
When an identity column is defined in a table, the server automatically assigns a unique sequential number for that identity column during inserts. To avoid excessive I/O, Adaptive Server Enterprise keeps blocks of numbers available in memoryfor use in identity values and keeps track of the current pointer in that block in memory.
The number of values in the block is controlled by the identity burning set factor. The identity column is a numeric datatype with a pre-defined precision. The server uses the possible range of values to determine which numbers are reserved.
For example, an identity column, "colA," is defined as numeric(7,0). The values of this column range from 1 to 9,999,999. Using the default burning set factor of 5000, .05% of the values are made available at a time. Therefore, the size of a block of numbers reserved in memory is .0005 (or .05%) x (9,999,999 +1) = 5000 values.
If the server shuts down unexpectedly, all unused numbers in the current reserved block (in memory) are lost. When the server restarts, the next insert starts with the next block. Therefore, abnormal shutdowns can cause large gaps in the identity column. An orderly shutdown (normal) does not delete identity values.
A shutdown with nowait is an unexpected shutdown and causes the block of numbers to be lost. For more information, see the Adaptive Server Enterprise System Administration Guide, the Transact-SQL User's Guide, and the Adaptive Server Enterprise Reference Guide.
Example: How Adaptive Server "loses" block values
In this example, we use the above identity column, colA, and perform a shutdown with nowait:
The starting value in the next block almost always will not be the first value in the new block. This is because of a current value that is stored in the dbtable info. When the block is lost, the server goes to the next block and the next value in the dbtable info. In the above case, the last value in the dbtable was 1.
Correcting identity column gaps
You can fix the rows containing the large numbers to correct gaps in the identity column. Perform the following steps:
The next identity number should be one greater than the largest identity number in the table.
Understanding the identity grab size
The identity grab size parameter allows a server process to reserve a block of identity column values for inserts into tables with identity columns.
What this means is that a particular server process gets its own block of numbers to use. This is useful if a particular server process needs to insert contiguous identity values. Identity grab size is a server-wide parameter, so do not set it to a high value. Set identity grab size only to a value large enough to accommodate the largest group of records you want to insert into contiguous rows. The default is 1.
Troubleshooting the identity burning set factor
In this example, performing shutdown with nowait confuses the user into believing that the identity burning set factor did not re-set.
This last step verifies that the identity burning set factor change worked.
New Identity Gap feature starting with ASE 12
The identity column contains a unique ID number, generated by Adaptive Server, for each row in a table. Because of the way the server generates ID numbers by default, you may have large gaps in the ID numbers. The identity_gap parameter gives you control over ID numbers, and potential gaps in them, for a specific table.
This parameter can be used when creating a table with create table or select into. To change the identity gap, use identity_gap with sp_chgattribute. To see the identity gap setting for a table, use sp_help.
Create Table Example:
about using identity_gap, see the System
Administration Guide and the command and system procedure
descriptions in the Adaptive Server Reference Manual.