Przyrost tożsamości skacze w bazie danych SQL Server


126

W jednej z moich tabel Feew kolumnie „ReceiptNo” w SQL Server 2012 przyrost tożsamości bazy danych nagle zaczął skakać do 100 zamiast 1, w zależności od następujących dwóch rzeczy.

  1. jeśli jest to 1205446, to skacze do 1206306, jeśli to 1206321, to skacze do 1207306, a jeśli jest to 1207314, to przeskakuje do 1208306. Chcę zauważyć, że ostatnie trzy cyfry pozostają stałe, tj. 306 za każdym razem, gdy skoki występuje, jak pokazano na poniższym rysunku.

  2. ten problem występuje po ponownym uruchomieniu komputera

wprowadź opis obrazu tutaj


Jeśli dodasz order by ReceiptNodo zapytania, czy naprawdę nie ma tych rekordów? Czy na pewno podczas wstawiania rekordów nie ma błędów? Jeśli rekord próbuje się wstawić i nie powiedzie się, tożsamość będzie się zwiększać, to samo, jeśli rekordy zostaną usunięte. Usunięcie rekordów ReceiptNonie powoduje zresetowania. Czy możesz opublikować tworzenie tabeli dla Feestołu?
Taryn

4
Pierwsze pytanie brzmi - dlaczego to ma znaczenie? powinien to być dowolny unikalny identyfikator
Andrew

1
Czy to działa na serwerze, czy może jest to wyraźne na pulpicie? Zastanawiasz się, dlaczego wydaje się, że usługa jest tak często restartowana?
Martin Smith,

@bluefeet Wiem, kiedy wystąpi błąd, następuje przyrost tożsamości. Jestem w 100% pewien, że nie ma błędów. Im edytuję moje pytanie, dodając tabelę i procedurę składowaną, której używam do wstawiania wierszy.
kashif,

@kashif - 99% pewności, że nie jest to potrzebne. Skoki przez dokładnie 1000 ( 1206306, 1207306, 1207806) oznacza, że wyjaśnienie w Connect poz wątek dotyczy prawie na pewno.
Martin Smith,

Odpowiedzi:


158

Takie zachowanie występuje z powodu poprawy wydajności od czasu programu SQL Server 2012.

Teraz domyślnie używa rozmiaru pamięci podręcznej 1000, gdy przydzielanie IDENTITYwartości dla intkolumny i ponowne uruchomienie usługi może „utracić” nieużywane wartości (rozmiar pamięci podręcznej wynosi 10 000 dla bigint/ numeric).

Jest to wspomniane w dokumentacji

SQL Server może buforować wartości tożsamości ze względu na wydajność, a niektóre przypisane wartości mogą zostać utracone podczas awarii bazy danych lub ponownego uruchomienia serwera. Może to spowodować luki w wartości tożsamości podczas wstawiania. Jeśli luki są nie do zaakceptowania, aplikacja powinna używać własnego mechanizmu do generowania wartości kluczy. Użycie generatora sekwencji z NOCACHEopcją może ograniczyć luki do transakcji, które nigdy nie zostały zatwierdzone.

Z przedstawionych danych wynika, że ​​miało to miejsce po wprowadzeniu danych z 22 grudnia, a następnie po ponownym uruchomieniu SQL Server zarezerwował wartości 1206306 - 1207305. Po wprowadzeniu danych z 24 - 25 grudnia nastąpił kolejny restart i SQL Server zarezerwował następny zakres 1207306 - 1208305widoczny we wpisach na 28 grudnia .

O ile nie restartujesz usługi z nietypową częstotliwością, jest mało prawdopodobne, aby jakiekolwiek „utracone” wartości spowodowały jakiekolwiek znaczące obniżenie zakresu wartości dozwolonych przez typ danych, więc najlepszą zasadą jest nie martwić się o to.

Jeśli z jakiegoś powodu jest to dla Ciebie prawdziwy problem, niektóre możliwe obejścia to ...

  1. Możesz użyć SEQUENCEkolumny tożsamości zamiast kolumny tożsamości i zdefiniować na przykład mniejszy rozmiar pamięci podręcznej i użyć NEXT VALUE FORw kolumnie domyślnej.
  2. Lub zastosuj flagę śledzenia 272, która powoduje, że IDENTITYalokacja jest rejestrowana tak, jak w wersjach do 2008 R2. Dotyczy to wszystkich baz danych na całym świecie.
  3. Lub w przypadku najnowszych wersji wykonaj, ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE = OFFaby wyłączyć buforowanie tożsamości dla określonej bazy danych.

Należy mieć świadomość, że żadne z tych obejść nie gwarantuje braku luk. Nigdy nie zostało to zagwarantowane, IDENTITYponieważ byłoby to możliwe tylko przez serializację wstawek do tabeli. Jeśli potrzebujesz kolumny bez przerw, będziesz musiał użyć innego rozwiązania niż jedno IDENTITYlub drugieSEQUENCE


1
aby sprawdzić, co powiedziałeś, wstawiłem kilka wartości i otrzymałem 1208309, 1208310, a następnie zrestartowałem serwer, a potem, gdy dodałem wiersz, otrzymałem 1209309, co oznacza, że ​​to, co powiedziałeś, jest absolutnie poprawne. wielkie dzięki. teraz czy możesz mi powiedzieć, jak mogę rozwiązać ten problem. czy zasugerowałbyś mi użycie SQL Server 2008 zamiast 2012, którego używałem wcześniej lub nawet używam 2012, ten problem można rozwiązać?
kashif,

1
@kashif - Czy to rzeczywiście problem dla Ciebie? Nawet jeśli dziennie zużyjesz 1000 wartości tożsamości, minie jeszcze 2 miliony dni, zanim skończą się wartości. Jeśli chcesz zachować stare zachowanie, możesz ustawić SQL Server na uruchamianie z flagą śledzenia 272 lub możesz użyć SEQUENCEzamiast a IDENTITYi ustawić Sekwencję tak, aby rozmiar pamięci podręcznej wynosił 0.
Martin Smith

Otrzymałem od ciebie dość imponujące i satysfakcjonujące odpowiedzi za moje rozwiązanie, dziękuję bardzo.
kashif,

Właściwie z twojego CREATE TABLEwidzę, że używasz numeric(7)i zacząłeś numerować od 1200001tego, co oznacza, że ​​skończysz się po 8,799dniach (24 latach), jeśli używasz 1000 dziennie.
Martin Smith

Rzeczywista wartość „przeskoczyła” powinna być zależna od użytego typu kolumny. Na przykład big intkolumna zwykle „przeskakuje” o 10 000 przy każdym ponownym uruchomieniu.
StarPilot

60

Ten problem występuje po ponownym uruchomieniu programu SQL Server.

Rozwiązaniem jest:

  • Uruchom Menedżera konfiguracji programu SQL Server .

  • Wybierz usługi SQL Server .

    Menedżer konfiguracji programu SQL Server

  • Kliknij prawym przyciskiem myszy SQL Server i wybierz Właściwości .

  • W oknie otwierającym w sekcji Parametry startowe wpisz -T272i kliknij Dodaj , a następnie naciśnij przycisk Zastosuj i uruchom ponownie.

    Parametry uruchamiania programu SQL Server


1
Ta metoda naprawdę działa, wielkie dzięki! i jak powiedziano tutaj , ten problem nie zostanie rozwiązany w SQL Server 2012, ani w jego dodatkach Service Pack, - tylko w następnej wersji.
Fragment

2
Czy istnieje sposób na zastosowanie flagi śledzenia do poszczególnych baz danych? Nie chcę wprowadzać tej zmiany na całym serwerze, ponieważ mam bazy danych innych firm i nie jestem pewien, jak to wpłynie na nie.
Ege Ersoz

1
Nie podążałem za powodami, ale najwyraźniej niektórzy użytkownicy musieli używać małych liter „t”, aby to zadziałało. Zobacz link zamieszczony przez Fragment w powyższym komentarzu.
Savage

33

Od SQL Server 2017+można użyć instrukcji ALTER DATABASE scoped KONFIGURACJA :

IDENTITY_CACHE = {ON | OFF}

Włącza lub wyłącza pamięć podręczną tożsamości na poziomie bazy danych. Domyślnie włączone. Buforowanie tożsamości służy do poprawy wydajności INSERT w tabelach z kolumnami tożsamości. Aby uniknąć luk w wartościach kolumny Identity w przypadkach, gdy serwer nieoczekiwanie uruchamia się ponownie lub przełącza się awaryjnie na serwer pomocniczy, wyłącz opcję IDENTITY_CACHE. Ta opcja jest podobna do istniejącej flagi śledzenia SQL Server 272, z tą różnicą, że można ją ustawić na poziomie bazy danych, a nie tylko na poziomie serwera.

(...)

G. Ustaw IDENTITY_CACHE

Ten przykład wyłącza pamięć podręczną tożsamości.

ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE=OFF ;

25

Wiem, że moja odpowiedź może się spóźnić na przyjęcie. Ale rozwiązałem to w inny sposób, dodając startową procedurę składowaną w SQL Server 2012.

Utwórz następującą procedurę składowaną w głównej bazie danych.

USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[ResetTableNameIdentityAfterRestart]
AS
BEGIN

begin TRAN
    declare @id int = 0
    SELECT @id =  MAX(id) FROM [DatabaseName].dbo.[TableName]
    --print @id
    DBCC CHECKIDENT ('[DatabaseName].dbo.[TableName]', reseed, @id)
Commit

END

Następnie dodaj go do Start up, używając następującej składni.

EXEC sp_procoption 'ResetOrderIdentityAfterRestart', 'startup', 'on';

To dobry pomysł, jeśli masz kilka stołów. ale jeśli musisz to zrobić dla wielu tabel, ta metoda nadal działa, ale nie jest dobrym pomysłem.


Dobry pomysł. Ale to nie działa dla tabel zależnych, prawda? Mam na myśli, czy to naprawia wartości klucza obcego?
rom5jp

@ rom5jp Naprawianie FK nie jest celem tej odpowiedzi. Chodzi o ustalenie możliwej następnej wartości PK w tabeli. Dopóki MAX (id) nie znajduje się w żadnym z FK, powinno działać.
Jeyara

14

Jest to nadal bardzo częsty problem wśród wielu programistów i aplikacji, niezależnie od rozmiaru.

Niestety powyższe sugestie nie rozwiązują wszystkich scenariuszy, np. Hosting współdzielony, nie możesz polegać na swoim hoście, aby ustawić parametr uruchamiania -t272.

Ponadto, jeśli masz istniejące tabele, które używają tych kolumn tożsamości jako kluczy podstawowych, OGROMNY wysiłek jest porzucenie tych kolumn i odtworzenie nowych, aby użyć obejścia sekwencji BS. Obejście sekwencyjne jest dobre tylko wtedy, gdy projektujesz tabele od podstaw w SQL 2012+

Najważniejsze jest to, że jeśli korzystasz z Sql Server 2008R2, POZOSTAŃ ON IT. Poważnie, trzymaj się tego. Dopóki Microsoft nie przyzna, że ​​wprowadził OGROMNY błąd, który nadal występuje nawet w Sql Server 2016, nie powinniśmy aktualizować, dopóki nie są właścicielami i NAPRAWIĆ.

Microsoft od razu wprowadził przełomową zmianę, tj. Złamał działające API, które nie działa już zgodnie z założeniami, ponieważ ich system zapomina o swojej aktualnej tożsamości przy ponownym uruchomieniu. Pamięć podręczna lub brak pamięci podręcznej, jest to niedopuszczalne, a deweloper firmy Microsoft o nazwisku Bryan musi ją posiadać, zamiast mówić światu, że jest to „zgodne z projektem” i „funkcją”. Jasne, buforowanie jest funkcją, ale utrata świadomości, jaka powinna być następna tożsamość, NIE JEST FUNKCJĄ. To niezły BŁĄD !!!

Podzielę się obejściem, którego użyłem, ponieważ moje bazy danych znajdują się na serwerach Shared Hosting, a także nie upuszczam i nie odtwarzam moich kolumn klucza podstawowego, to byłby ogromny PITA.

Zamiast tego jest to mój haniebny hack (ale nie tak haniebny, jak ten błąd POS, który wprowadził Microsoft).

Hack / Fix:

Przed wykonaniem poleceń wstawiania po prostu ponownie ustaw swoją tożsamość przed każdym wstawieniem. Ta poprawka jest zalecana tylko wtedy, gdy nie masz kontroli administratora nad instancją Sql Server, w przeciwnym razie sugeruję ponowne wprowadzenie po ponownym uruchomieniu serwera.

declare @newId int -- where int is the datatype of your PKey or Id column
select @newId = max(YourBuggedIdColumn) from YOUR_TABLE_NAME
DBCC CheckIdent('YOUR_TABLE_NAME', RESEED, @newId)

Tylko te 3 wiersze bezpośrednio przed wstawką i powinno być dobrze. Naprawdę nie wpłynie to tak bardzo na wydajność, tj. Będzie niezauważalne.

Powodzenia.


7

Istnieje wiele możliwych powodów przeskakiwania wartości tożsamości. Obejmują one od wycofanych wstawek po zarządzanie tożsamością na potrzeby replikacji. Co jest tego przyczyną w twoim przypadku, nie mogę powiedzieć bez spędzenia czasu w twoim systemie.

Powinieneś jednak wiedzieć, że w żadnym wypadku nie możesz założyć, że kolumna tożsamości jest ciągła. Jest po prostu zbyt wiele rzeczy, które mogą powodować luki.

Więcej informacji na ten temat można znaleźć tutaj: http://sqlity.net/en/792/the-gap-in-the-identity-value-sequence/

Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.