In some occasions the database has given me jump we say that of going in 200,000 until 250,000 or even greater. Some idea of how I can do a log search, to determine the problem, some recommendations of possible causes or information that will help me more about it. ?
At first glance, I would say that you do not use identity if you require a guarantee that your primary keys do not have "holes"; that is, missing numbers in the sequence, but thinking about it better, I would say that I do not see a reason to impose such a restriction on a real life system.
I'll explain about the holes.
I will assume that we are talking about a system that
In summary, it is easy to get holes in the primary key simply by having transactions that end with a rollback and not commit . I think an example is worth a thousand words.
create table TestIdentity (
IDColumn int identity(1, 1) primary key
, Numero int
);
commit;
go
begin transaction
insert into TestIdentity (Numero) values (1);
insert into TestIdentity (Numero) values (1);
insert into TestIdentity (Numero) values (1);
insert into TestIdentity (Numero) values (1);
commit;
go
begin transaction
insert into TestIdentity (Numero) values (2);
insert into TestIdentity (Numero) values (2);
insert into TestIdentity (Numero) values (2);
insert into TestIdentity (Numero) values (2);
rollback;
go
begin transaction
insert into TestIdentity (Numero) values (3);
insert into TestIdentity (Numero) values (3);
insert into TestIdentity (Numero) values (3);
insert into TestIdentity (Numero) values (3);
commit;
go
select *
from TestIdentity;
go
The result of the last selection is:
IDColumn Numero
----------- -----------
1 1
2 1
3 1
4 1
9 3
10 3
11 3
12 3
As you can see, 4 numbers are suddenly missing in our primary key. These were lost in the rollback transaction.
Another small test with concurrency, that is, several users that insert records simultaneously, requires that you use two different connections (for example, two tabs in the SQL Server Management Studio ,
First, I execute the begin transaction statement on both connections, then alternately the insert into TestIdentity (Numero) values (5); statement on the first tab and the insert into TestIdentity (Numero) values (6); statement on the second tab.
I execute it, say 3 times in the first, 3 times in the second, but one by one, that is, I go to the first, execute, then to the second, execute, return to the first, execute, and so on .
Then in the first execution the statement commit and in the second the statement rollback . A new select on the table throws me now:
IDColumn Numero
----------- -----------
1 1
2 1
3 1
4 1
9 3
10 3
11 3
12 3
13 4
15 4
17 4
If you observe, only the records with number 4 remain, but the primary key is jumping, where there is a number left between each value.
SQL Server 2012, for example, uses a cache of 1,000 for the IDENTITY values in columns of type int . When you restart the service, the unused cached values are lost. (The cache size is 10,000 for bigint / numeric ).
The Documentation says:
Consecutive values after a server reboot or other errors : SQL Server could cache the identity values for performance reasons and some of the assigned values could be lost during an error in the database or a restart of the server. This may result in spaces in the identity value when inserted. If it is not acceptable that there are spaces, the application must use its own mechanisms to generate key values. The use of a sequence generator with the NOCACHE option can limit spaces to transactions that never take place.
It is likely that there are other scenarios where more holes are generated, now they do not come to mind, but the most common are these two.
Finding a jump of 50,000 records is nothing more than evidence that
rollback