TIP: Understanding why the identity field sometimes jumps massively (

Braveheart · May 28, 1999

TIP: Understanding why the identity field sometimes jumps massively (I've seen 30 quadrillion) 
 
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. 
 
Note: 
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: 
 
1.After 5 inserts into the table we have: 
 
colA 
------- 
1 
2 
3 
4 
5 
 
 
2.We now shut down the server with nowait, then re-start the server. 
 
select colA from table: 
 
colA 
------- 
1 
2 
3 
4 
5 
 
3.We perform an insert into the table, then select colA again: 
 
colA 
------- 
1 
2 
3 
4 
5 
5002 
 
4.If we perform a shutdown with nowait again, then repeat the insert and select colA again, colA displays as 
follows: 
 
colA 
------- 
1 
2 
3 
4 
5 
5002 
10003 
 
 
Note: 
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: 
 
1.Turn on identity_insert. 
 
1> set identity_insert table_name on 
 
2> go 
 
2.Record the row(s) information. Because you cannot update an identity column, you must delete the row(s) and insert them again with the correct identity values. 
 
3.Correct all rows in error. 
 
4.bcp out the data from the table. 
 
5.Turn off identity_insert. 
 
1> set identity_insert table_name off 
 
2> go 
 
6.Drop the table and re-create it. This step resets the identity column's auto-start number to 1. 
 
7.bcp the data back into the table using the -E option to maintain the identity values that appear in the host file. 
 
Note: 
The -E option tells the server not to use the auto-insert 
identity numbers and to use the identity numbers included 
in the bcp data. 
 
 
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. 
 
0800 hrs. Create table burn1(col1 char(4), col2 numeric identity). Inserted several rows. Select * from burn1 returns: 
 
col1 col2 
---- -------------------- 
aaaa 1 
aaab 2 
aaac 3 
aaad 4 
 
When the first insert is done, a block of 500000000000000 numbers is taken as the first block of numbers. 
 
0815 hrs. shutdown with nowait. 
 
With the nowait, the server is unable to write the current identity number back to disk, so the rest of the 
500000000000000 block of numbers is lost. 
 
0816 hrs. startserver 
 
0817 hrs. insert 1 row into burn1. select * returns: 
 
col1 col2 
---- -------------------- 
aaaa 1 
aaab 2 
aaac 3 
aaad 4 
aaae 500000000000002 
 
As expected, we lost the first block of 5000000000000 numbers. The insert of 1 row above causes the server to get the next block of 500000000000000 numbers. 
 
0818 hrs. Change identity burning set factorfrom 5000 to 10. 
 
1> sp_configure "identity burning set factor", 10 
2> go 
 
Configuration option changed. (return status = 0) 
 
1> sp_configure 
2> go 
 
name minimum maximum config_ 
value run_value 
---------------------------- ----------- ----------- ------------ ---- 
------- 
... 
... 
identity burning set factor 1 9999999 10 
5000 
 
0820 hrs. shutdown with nowait again. 
 
With the nowait, the server is unable to write the current identity number back to disk, so the rest of the second 500000000000000 block of numbers is lost. 
 
0822 hrs. startserver 
 
0823 hrs. Check that identity burning set factor has changed. 
 
1> sp_configure 
2> go 
 
name minimum maximum config_ 
value run_value 
---------------------------- ----------- ------- 
---- ------------ ----------- 
... 
... 
identity burning set factor 1 9999999 10 
10 
 
0825 hrs. insert 1 row into burn1. select * returns: 
 
col1 col2 
---- -------------------- 
aaaa 1 
aaab 2 
aaac 3 
aaad 4 
aaae 500000000000002 
aaaf 1000000000000003 
 
As expected, the server lost the second block of 5000000000000 numbers. The insert of 1 row above causes the server to get the next block of 1000000000000 numbers. 
 
Note: 
The above output from the select, which shows that the 
server deleted the second large block of numbers, often 
confuses users into believing that no change to the identity 
parameter occurred. 
 
 
0827 hrs. shutdown with nowait again. 
 
0830 hrs. startserver 
 
0825 hrs. insert 1 row into burn1. select * displays: 
 
col1 col2 
---- -------------------- 
aaaa 1 
aaab 2 
aaac 3 
aaad 4 
aaae 500000000000002 
aaaf 1000000000000003 
aaag 1001000000000004 
 
As expected, the server lost the first block of 10000000000 numbers. The insert of 1 row above causes the server to get the next block of 1000000000000 numbers. 
 
This last step verifies that the identity burning set factor change worked. 
 
Happy Sybaseing, Brian

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

TIP: Understanding why the identity field sometimes jumps massively (

Braveheart

Programmer

Similar threads

Part and Inventory Search

Sponsor