Dlaczego sekwencyjne klucze GUID działają szybciej niż sekwencyjne klucze INT w moim przypadku testowym?

Po zadaniu tego pytania porównując sekwencyjne i niesekwencyjne identyfikatory GUID, próbowałem porównać wydajność INSERT na 1) tabeli z kluczem podstawowym GUID inicjowanym sekwencyjnie newsequentialid(), oraz 2) tabeli z kluczem podstawowym INT inicjowanym sekwencyjnie identity(1,1). Spodziewałbym się, że ta ostatnia będzie najszybsza z powodu mniejszej szerokości liczb całkowitych, a także wydaje się łatwiejsze wygenerowanie sekwencyjnej liczby całkowitej niż sekwencyjny identyfikator GUID. Ale ku mojemu zdziwieniu, WSTAWKI na stole z kluczem całkowitym były znacznie wolniejsze niż sekwencyjna tabela GUID.

To pokazuje średni czas użycia (ms) dla testów:

NEWSEQUENTIALID()  1977
IDENTITY()         2223

Czy ktoś może to wyjaśnić?

Zastosowano następujący eksperyment:

SET NOCOUNT ON

CREATE TABLE TestGuid2 (Id UNIQUEIDENTIFIER NOT NULL DEFAULT NEWSEQUENTIALID() PRIMARY KEY,
SomeDate DATETIME, batchNumber BIGINT, FILLER CHAR(100))

CREATE TABLE TestInt (Id Int NOT NULL identity(1,1) PRIMARY KEY,
SomeDate DATETIME, batchNumber BIGINT, FILLER CHAR(100))

DECLARE @BatchCounter INT = 1
DECLARE @Numrows INT = 100000


WHILE (@BatchCounter <= 20)
BEGIN 
BEGIN TRAN

DECLARE @LocalCounter INT = 0

    WHILE (@LocalCounter <= @NumRows)
    BEGIN
    INSERT TestGuid2 (SomeDate,batchNumber) VALUES (GETDATE(),@BatchCounter)
    SET @LocalCounter +=1
    END

SET @LocalCounter = 0

    WHILE (@LocalCounter <= @NumRows)
    BEGIN
    INSERT TestInt (SomeDate,batchNumber) VALUES (GETDATE(),@BatchCounter)
    SET @LocalCounter +=1
    END

SET @BatchCounter +=1
COMMIT 
END

DBCC showcontig ('TestGuid2')  WITH tableresults
DBCC showcontig ('TestInt')  WITH tableresults

SELECT batchNumber,DATEDIFF(ms,MIN(SomeDate),MAX(SomeDate)) AS [NEWSEQUENTIALID()]
FROM TestGuid2
GROUP BY batchNumber

SELECT batchNumber,DATEDIFF(ms,MIN(SomeDate),MAX(SomeDate)) AS [IDENTITY()]
FROM TestInt
GROUP BY batchNumber

DROP TABLE TestGuid2
DROP TABLE TestInt

AKTUALIZACJA: Modyfikując skrypt w celu wykonywania wstawień w oparciu o tabelę TEMP, jak w przykładach autorstwa Phila Sandlera, Mitcha Wheata i Martina poniżej, stwierdzam również, że TOŻSAMOŚĆ jest szybsza, jak powinna. Ale to nie jest konwencjonalny sposób wstawiania wierszy i nadal nie rozumiem, dlaczego eksperyment zawiódł na początku: nawet jeśli pominę GETDATE () w moim oryginalnym przykładzie, TOŻSAMOŚĆ () jest nadal znacznie wolniejsza. Wydaje się więc, że jedynym sposobem na osiągnięcie lepszej wydajności IDENTITY () NEWSEQUENTIALID () jest przygotowanie wierszy do wstawienia do tabeli tymczasowej i wykonanie wielu wstawień jako wsadowego wsadu przy użyciu tej tabeli temp. Podsumowując, nie sądzę, że znaleźliśmy wyjaśnienie tego zjawiska, a TOŻSAMOŚĆ () wydaje się być wolniejsza w przypadku większości praktycznych zastosowań. Czy ktoś może to wyjaśnić?

Zmodyfikowałem kod @Phil Sandler, aby usunąć efekt wywołania GETDATE () (mogą występować efekty / przerwania sprzętowe ??), i ustawiłem wiersze tej samej długości.

[Od czasu SQL Server 2000 pojawiło się kilka artykułów dotyczących problemów z timerem i timerów o wysokiej rozdzielczości, więc chciałem zminimalizować ten efekt.]

W prostym modelu odzyskiwania z danymi i plikiem dziennika, zarówno o rozmiarach przekraczających wymagane, oto czasy (w sekundach): (Zaktualizowano o nowe wyniki na podstawie dokładnego kodu poniżej)

       Identity(s)  Guid(s)
       ---------    -----
       2.876        4.060    
       2.570        4.116    
       2.513        3.786   
       2.517        4.173    
       2.410        3.610    
       2.566        3.726
       2.376        3.740
       2.333        3.833
       2.416        3.700
       2.413        3.603
       2.910        4.126
       2.403        3.973
       2.423        3.653
    -----------------------
Avg    2.650        3.857
StdDev 0.227        0.204

Zastosowany kod:

SET NOCOUNT ON

CREATE TABLE TestGuid2 (Id UNIQUEIDENTIFIER NOT NULL DEFAULT NEWSEQUENTIALID() PRIMARY KEY,
SomeDate DATETIME, batchNumber BIGINT, FILLER CHAR(88))

CREATE TABLE TestInt (Id Int NOT NULL identity(1,1) PRIMARY KEY,
SomeDate DATETIME, batchNumber BIGINT, FILLER CHAR(100))

DECLARE @Numrows INT = 1000000

CREATE TABLE #temp (Id int NOT NULL Identity(1,1) PRIMARY KEY, rowNum int, adate datetime)

DECLARE @LocalCounter INT = 0

--put rows into temp table
WHILE (@LocalCounter < @NumRows)
BEGIN
    INSERT INTO #temp(rowNum, adate) VALUES (@LocalCounter, GETDATE())
    SET @LocalCounter += 1
END

--Do inserts using GUIDs
DECLARE @GUIDTimeStart DateTime = GETDATE()
INSERT INTO TestGuid2 (SomeDate, batchNumber) 
SELECT adate, rowNum FROM #temp
DECLARE @GUIDTimeEnd  DateTime = GETDATE()

--Do inserts using IDENTITY
DECLARE @IdTimeStart DateTime = GETDATE()
INSERT INTO TestInt (SomeDate, batchNumber) 
SELECT adate, rowNum FROM #temp
DECLARE @IdTimeEnd DateTime = GETDATE()

SELECT DATEDIFF(ms, @IdTimeStart, @IdTimeEnd) AS IdTime, DATEDIFF(ms, @GUIDTimeStart, @GUIDTimeEnd) AS GuidTime

DROP TABLE TestGuid2
DROP TABLE TestInt
DROP TABLE #temp
GO

Po przeczytaniu śledztwa @ Martina w obu przypadkach ponownie uruchomiłem sugerowany TOP (@num), tj

...
--Do inserts using GUIDs
DECLARE @num INT = 2147483647; 
DECLARE @GUIDTimeStart DATETIME = GETDATE(); 
INSERT INTO TestGuid2 (SomeDate, batchNumber) 
SELECT TOP(@num) adate, rowNum FROM #temp; 
DECLARE @GUIDTimeEnd DATETIME = GETDATE();

--Do inserts using IDENTITY
DECLARE @IdTimeStart DateTime = GETDATE()
INSERT INTO TestInt (SomeDate, batchNumber) 
SELECT TOP(@num) adate, rowNum FROM #temp;
DECLARE @IdTimeEnd DateTime = GETDATE()
...

a oto wyniki pomiaru czasu:

       Identity(s)  Guid(s)
       ---------    -----
       2.436        2.656
       2.940        2.716
       2.506        2.633
       2.380        2.643
       2.476        2.656
       2.846        2.670
       2.940        2.913
       2.453        2.653
       2.446        2.616
       2.986        2.683
       2.406        2.640
       2.460        2.650
       2.416        2.720

    -----------------------
Avg    2.426        2.688
StdDev 0.010        0.032

Nie byłem w stanie uzyskać rzeczywistego planu wykonania, ponieważ zapytanie nigdy nie powróciło! Wygląda na to, że błąd jest prawdopodobny. (Z systemem Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64))

Na świeżej bazie danych w prostym modelu odzyskiwania z plikiem danych o wielkości 1 GB i plikiem dziennika o wielkości 3 GB (laptop, oba pliki na tym samym dysku) i interwałem odzyskiwania ustawionym na 100 minut (aby uniknąć wypaczenia wyników przez punkt kontrolny) Widzę wyniki podobne do ciebie w jednym rzędzie inserts.

Przetestowałem trzy przypadki: Dla każdego przypadku wykonałem 20 partii wstawiając 100 000 wierszy indywidualnie do poniższych tabel. Pełne skrypty można znaleźć w historii zmian tej odpowiedzi .

CREATE TABLE TestGuid
  (
     Id          UNIQUEIDENTIFIER NOT NULL DEFAULT NEWSEQUENTIALID() PRIMARY KEY,
     SomeDate    DATETIME, batchNumber BIGINT, FILLER CHAR(100)
  )

CREATE TABLE TestId
  (
     Id          Int NOT NULL identity(1, 1) PRIMARY KEY,
     SomeDate    DATETIME, batchNumber BIGINT, FILLER CHAR(100)
  )

CREATE TABLE TestInt
  (
     Id          Int NOT NULL PRIMARY KEY,
     SomeDate    DATETIME, batchNumber BIGINT, FILLER  CHAR(100)
  )  

W przypadku trzeciej tabeli test wstawił wiersze o Idwartości rosnącej, ale została ona obliczona samodzielnie przez zwiększenie wartości zmiennej w pętli.

Uśrednianie czasu 20 partii dało następujące wyniki.

NEWSEQUENTIALID() IDENTITY()  INT
----------------- ----------- -----------
1999              2633        1878

Wniosek

Zdecydowanie wydaje się, że to narzut związany z identityprocesem tworzenia odpowiedzialny za wyniki. W przypadku samodzielnie obliczanej inkrementującej liczby całkowitej wyniki są znacznie bardziej zgodne z tym, czego można się spodziewać, biorąc pod uwagę tylko koszt IO.

Po umieszczeniu opisanego powyżej kodu wstawiania w procedurach przechowywanych i przejrzeniu sys.dm_exec_procedure_statsdaje on następujące wyniki

proc_name      execution_count      total_worker_time    last_worker_time     min_worker_time      max_worker_time      total_elapsed_time   last_elapsed_time    min_elapsed_time     max_elapsed_time     total_physical_reads last_physical_reads  min_physical_reads   max_physical_reads   total_logical_writes last_logical_writes  min_logical_writes   max_logical_writes   total_logical_reads  last_logical_reads   min_logical_reads    max_logical_reads
-------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- --------------------
IdentityInsert 20                   45060360             2231067              2094063              2645079              45119362             2234067              2094063              2660080              0                    0                    0                    0                    32505                1626                 1621                 1626                 6268917              315377               276833               315381
GuidInsert     20                   34829052             1742052              1696051              1833055              34900053             1744052              1698051              1838055              0                    0                    0                    0                    35408                1771                 1768                 1772                 6316837              316766               298386               316774

Tak więc w tych wynikach total_worker_timejest o około 30% wyższy. To reprezentuje

Całkowity czas procesora w mikrosekundach, który został wykorzystany przez wykonanie tej procedury składowanej od czasu jej skompilowania.

Wygląda więc po prostu tak, jakby kod generujący IDENTITYwartość był bardziej obciążający procesor niż ten, który generuje NEWSEQUENTIALID()(Różnica między 2 liczbami wynosi 10231308, co wynosi średnio około 5µs na wkładkę.) I że dla tej definicji tabeli ten stały koszt procesora był wystarczająco wysoki, aby przewyższyć dodatkowe logiczne odczyty i zapisy powstałe z powodu większej szerokości klucza. (Uwaga: Itzik Ben Gan zrobił tutaj podobne testy i znalazł karę 2µs za wkładkę)

Dlaczego więc wymaga IDENTITYwięcej procesora UuidCreateSequential?

Wierzę, że wyjaśniono to w tym artykule . Dla każdej dziesiątej identitywygenerowanej wartości SQL Server musi zapisać zmianę w tabelach systemowych na dysku

Co z wkładkami MultiRow?

Kiedy wstawiono 100 000 wierszy w jednym stwierdzeniu, zauważyłem, że różnica zniknęła, co może być nadal niewielką korzyścią dla GUIDsprawy, ale nie jest tak bliskie rezultatu. Średnia dla 20 partii w moim teście wyniosła

NEWSEQUENTIALID() IDENTITY()
----------------- -----------
1016              1088

Powodem, dla którego nie ma widocznej kary w kodzie Phila i pierwszym zestawie wyników Mitcha jest to, że tak się złożyło, że kod, który użyłem do wykonania wielowierszowej wstawki SELECT TOP (@NumRows). Uniemożliwiło to optymalizatorowi prawidłowe oszacowanie liczby wierszy, które zostaną wstawione.

Wydaje się to być korzystne, ponieważ istnieje pewien punkt krytyczny, w którym doda on dodatkową operację sortowania dla (podobno sekwencyjnych!) GUIDS.

Ta operacja sortowania nie jest wymagana z tekstu objaśniającego w BOL .

Tworzy GUID, który jest większy niż GUID wcześniej wygenerowany przez tę funkcję na określonym komputerze od czasu uruchomienia systemu Windows. Po ponownym uruchomieniu systemu Windows identyfikator GUID można uruchomić ponownie z niższego zakresu, ale nadal jest unikalny na całym świecie.

Wydawało mi się więc, że błąd lub brak optymalizacji polega na tym, że SQL Server nie rozpoznaje, że dane wyjściowe skalaru obliczeniowego będą już wstępnie posortowane, tak jak najwyraźniej już dla identitykolumny. ( Edytuj Zgłosiłem to, a problem niepotrzebnego sortowania został rozwiązany w Denali )

Całkiem proste: z GUID tańsze jest wygenerowanie następnego numeru w wierszu niż w przypadku TOŻSAMOŚCI (bieżąca wartość GUID nie musi być przechowywana, TOŻSAMOŚĆ musi być). Dotyczy to nawet NEWSEQUENTIALGUID.

Możesz sprawić, by test był bardziej uczciwy i użyć SEKWENCERA z dużym DACHEM - który jest tańszy niż TOŻSAMOŚĆ.

Ale jak mówi MR, istnieją pewne główne zalety GUID. W rzeczywistości są one DUŻO bardziej skalowalne niż kolumny TOŻSAMOŚCI (ale tylko wtedy, gdy NIE są one sekwencyjne).

Zobacz: http://blog.kejser.org/2011/10/05/boosting-insert-speed-by-generating-scalable-keys/

Fascynuje mnie tego rodzaju pytanie. Dlaczego musiałeś to opublikować w piątek wieczorem? :)

Myślę, że nawet jeśli twój test ma WYŁĄCZNIE mierzyć wydajność WSTAWIANIA, możesz (prawdopodobnie) wprowadziłeś wiele czynników, które mogą wprowadzać w błąd (zapętlenie, długotrwała transakcja itp.)

Nie jestem do końca przekonany, że moja wersja coś udowadnia, ale tożsamość działa lepiej niż zawarte w niej identyfikatory GUID (3,2 sekundy w porównaniu z 6,8 sekundy na komputerze domowym):

SET NOCOUNT ON

CREATE TABLE TestGuid2 (Id UNIQUEIDENTIFIER NOT NULL DEFAULT NEWSEQUENTIALID() PRIMARY KEY,
SomeDate DATETIME, batchNumber BIGINT, FILLER CHAR(100))

CREATE TABLE TestInt (Id Int NOT NULL identity(1,1) PRIMARY KEY,
SomeDate DATETIME, batchNumber BIGINT, FILLER CHAR(100))

DECLARE @Numrows INT = 1000000

CREATE TABLE #temp (Id int NOT NULL Identity(1,1) PRIMARY KEY, rowNum int)

DECLARE @LocalCounter INT = 0

--put rows into temp table
WHILE (@LocalCounter < @NumRows)
BEGIN
    INSERT INTO #temp(rowNum) VALUES (@LocalCounter)
    SET @LocalCounter += 1
END

--Do inserts using GUIDs
DECLARE @GUIDTimeStart DateTime = GETDATE()
INSERT INTO TestGuid2 (SomeDate, batchNumber) 
SELECT GETDATE(), rowNum FROM #temp
DECLARE @GUIDTimeEnd  DateTime = GETDATE()

--Do inserts using IDENTITY
DECLARE @IdTimeStart DateTime = GETDATE()
INSERT INTO TestInt (SomeDate, batchNumber) 
SELECT GETDATE(), rowNum FROM #temp
DECLARE @IdTimeEnd DateTime = GETDATE()

SELECT DATEDIFF(ms, @IdTimeStart, @IdTimeEnd) AS IdTime
SELECT DATEDIFF(ms, @GUIDTimeStart, @GUIDTimeEnd) AS GuidTime

DROP TABLE TestGuid2
DROP TABLE TestInt
DROP TABLE #temp

Uruchomiłem twój przykładowy skrypt kilka razy, wprowadzając kilka drobnych poprawek do liczby i wielkości partii (i bardzo dziękuję za jej dostarczenie).

Najpierw powiem, że mierzysz tylko jeden aspekt wydajności klawiszy - INSERTszybkość. Tak więc, chyba że zajmujesz się tylko szybkim wprowadzaniem danych do tabel, zwierzę to ma znacznie więcej.

Moje ustalenia były ogólnie podobne do twoich. Chciałbym jednak wspomnieć, że wariancja INSERTprędkości pomiędzy GUIDi IDENTITY(int) jest nieco większa w przypadku GUIDniż w przypadku IDENTITY- może +/- 10% między biegami. Użyte partie za IDENTITYkażdym razem różniły się mniej niż 2–3%.

Warto również zauważyć, że moje pole testowe jest wyraźnie słabsze niż twoje, więc musiałem użyć mniejszej liczby wierszy.

Odniosę się do innej konwekcji dotyczącej stackoverflow dla tego samego tematu - https://stackoverflow.com/questions/170346/what-are-the-performance-improvement-of-sequential-guid-over-standard-guid

Wiem tylko, że sekwencyjne identyfikatory GUID polegają na tym, że użycie indeksu jest lepsze ze względu na bardzo niewielki ruch liści, a tym samym ograniczenie wyszukiwania HD. Pomyślałbym, że z tego powodu wstawki również byłyby szybsze, ponieważ nie musiałby rozdzielać kluczy na dużej liczbie stron.

Moje osobiste doświadczenie polega na tym, że gdy wdrażasz bazę danych o dużym ruchu, lepiej jest używać identyfikatorów GUID, ponieważ sprawia, że ​​jest ona znacznie bardziej skalowalna w celu integracji z innymi systemami. Dotyczy to w szczególności replikacji i limitów int / bigint ... nie dlatego, że zabraknie bigintów, ale w końcu to zrobisz i cofniesz się.