Odkąd został napisany artykuł Itzika Bena Gana, rozmiar pamięci podręcznej na 10 dla IDENTITYwydaje się być zmieniony. Z komentarzy na temat tego elementu połączenia
Rozmiar alokacji wstępnej zależy od wielkości typu danych kolumny, w której zdefiniowano właściwość tożsamości. W przypadku kolumny liczb całkowitych programu SQL Server serwer wstępnie przydziela tożsamości w zakresie 1000 wartości. Dla typu danych bigint serwer wstępnie przydziela w zakresach 10000 wartości.
T-SQL Zapytania książka zawiera poniższa tabela, ale podkreśla, że wartości te nie są udokumentowane lub gwarancją niezmienione.
+-----------------+-----------+
| DataType | CacheSize |
+-----------------+-----------+
| TinyInt | 10 |
| SmallInt | 100 |
| Int | 1,000 |
| BigInt, Numeric | 10,000 |
+-----------------+-----------+
Artykuł tutaj testuje różne rozmiary pamięci podręcznej sekwencji i wstawia rozmiary partii i przedstawia następujące wyniki.
Co wydaje się wskazywać, że w przypadku dużych wkładek IDENTITYwykonuje się SEQUENCE. Jednak nie testuje rozmiaru pamięci podręcznej 1000, a także te wyniki są tylko jednym testem. Patrząc konkretnie na wielkość pamięci podręcznej 1000 z różnymi rozmiarami partii wkładek, otrzymałem następujące wyniki (próbowanie każdej wielkości partii 50 razy i agregowanie wyników jak poniżej - cały czas w μs).
+------------+-----------+-----------+-----------+-----------+-----------+-----------+
| | Sequence | Identity |
| Batch Size | Min | Max | Avg | Min | Max | Avg |
+------------+-----------+-----------+-----------+-----------+-----------+-----------+
| 10 | 2,994 | 7,004 | 4,002 | 3,001 | 7,005 | 4,022 |
| 100 | 3,997 | 5,005 | 4,218 | 4,001 | 5,010 | 4,238 |
| 1,000 | 6,001 | 19,013 | 7,221 | 5,982 | 8,006 | 6,709 |
| 10,000 | 26,999 | 33,022 | 28,645 | 24,015 | 34,022 | 26,114 |
| 100,000 | 189,126 | 293,340 | 205,968 | 165,109 | 234,156 | 173,391 |
| 1,000,000 | 2,208,952 | 2,344,689 | 2,269,297 | 2,058,377 | 2,191,465 | 2,098,552 |
+------------+-----------+-----------+-----------+-----------+-----------+-----------+
W przypadku większych partii partia IDENTITYwydaje się generalnie szybsza .
Książka zapytań TSQL wyjaśnia również, dlaczego IDENTITYmoże mieć przewagę wydajności nad sekwencją.
Jest IDENTITYto specyficzne dla tabeli i SEQUENCEnie jest. Jeśli katastrofa miała nastąpić w trakcie wstawiania przed opróżnieniem bufora dziennika, nie ma znaczenia, czy odzyskana tożsamość jest wcześniejsza, ponieważ proces odzyskiwania również cofnie wstawienie, więc SQL Server nie wymusza opróżniania bufora dziennika przy każdej tożsamości zapis dysku związany z pamięcią podręczną. Jednak w przypadku sekwencji jest to wymuszane, ponieważ wartość może być używana w dowolnym celu - w tym poza bazą danych. Tak więc w powyższym przykładzie z milionem wstawek i wielkości pamięci podręcznej 1000 jest to dodatkowy tysiąc logów.
Skrypt do odtworzenia
DECLARE @Results TABLE(
BatchCounter INT,
NumRows INT,
SequenceTime BIGINT,
IdTime BIGINT);
DECLARE @NumRows INT = 10,
@BatchCounter INT;
WHILE @NumRows <= 1000000
BEGIN
SET @BatchCounter = 0;
WHILE @BatchCounter <= 50
BEGIN
--Do inserts using Sequence
DECLARE @SequenceTimeStart DATETIME2(7) = SYSUTCDATETIME();
INSERT INTO dbo.t1_Seq1_cache_1000
(c1)
SELECT N
FROM [dbo].[TallyTable] (@NumRows)
OPTION (RECOMPILE);
DECLARE @SequenceTimeEnd DATETIME2(7) = SYSUTCDATETIME();
--Do inserts using IDENTITY
DECLARE @IdTimeStart DATETIME2(7) = SYSUTCDATETIME();
INSERT INTO dbo.t1_identity
(c1)
SELECT N
FROM [dbo].[TallyTable] (@NumRows)
OPTION (RECOMPILE);
DECLARE @IdTimeEnd DATETIME2(7) = SYSUTCDATETIME();
INSERT INTO @Results
SELECT @BatchCounter,
@NumRows,
DATEDIFF(MICROSECOND, @SequenceTimeStart, @SequenceTimeEnd) AS SequenceTime,
DATEDIFF(MICROSECOND, @IdTimeStart, @IdTimeEnd) AS IdTime;
TRUNCATE TABLE dbo.t1_identity;
TRUNCATE TABLE dbo.t1_Seq1_cache_1000;
SET @BatchCounter +=1;
END
SET @NumRows *= 10;
END
SELECT NumRows,
MIN(SequenceTime) AS MinSequenceTime,
MAX(SequenceTime) AS MaxSequenceTime,
AVG(SequenceTime) AS AvgSequenceTime,
MIN(IdTime) AS MinIdentityTime,
MAX(IdTime) AS MaxIdentityTime,
AVG(IdTime) AS AvgIdentityTime
FROM @Results
GROUP BY NumRows;