.NET und PDF Software

Kurz nach der Migration einiger Datenbank von einem SQL Server 2008 auf einen SQL Server 2016 ist aufgefallen, dass es große Sprünge in den Identity-Spalten gab, die nicht durch verworfene Datensätze erklärt werden konnten. Dabei ging es jeweils um Sprünge von ca. 1000 oder sogar 10.000. Die Ursache ist in der MSDN zu finden:

The identity property on a column does not guarantee the following: … Consecutive values after server restart or other failures – SQL Server might cache identity values for performance reasons and some of the assigned values can be lost during a database failure or server restart. This can result in gaps in the identity value upon insert.

Dieser Sprung beträgt bei Spalten mit bigint aber bis zu 10.000, bei Spalten mit int immerhin noch 1.000. Da ist sehr viel und bringt einen in einen Erklärungsbedarf. Basierend auf diesem Beitrag lässt sich das Problem leicht nachstellen:

CREATE TABLE MyTestTable(Id INT IDENTITY(1,1), Name VARCHAR(255));
 
DECLARE @i INT = 100000
 
While @i > 0
BEGIN
    INSERT INTO MytestTable (Name) VALUES ('Test2')
    SET @i = @i-1
END

Während diese Schleife läuft (das Problem tritt nur bei Tabellen in Verwendung auf!), den SQL Server neu starten. Dann

INSERT INTO MyTestTable(Name) VALUES ('Mr.Jackson'); 
 
SELECT Id, Name FROM MyTestTable ORDER BY Id DESC;

Damit sollte das Problem sichtbar sein. Eine Option ist die Nutzung von Sequenzen mit der NOCACHE Option, anstatt auf IDENTITY Spalten zu setzen. Wenn diese Option zu aufwendig ist, kann auch die Option -T272 für die komplette Instanz genutzt werden. Damit ist das alte Verhalten (bis SQL 2008) wiederhergestellt.

Es bleibt noch anzumerken, dass das TRACE Flag T272 das Caching-Verhalten für die Generierung von Identity-Werten deaktiviert. Negative Auswirkungen auf die Performance beim Erzeugen neuer Datensätze sind somit möglich.