Sekwencja - BRAK DACHU vs DACH 1

Czy jest jakaś różnica między SEQUENCEzadeklarowanym za pomocą NO CACHEa zadeklarowanym za pomocą CACHE 1w SQL Server 2012+?

Sekwencja nr 1:

CREATE SEQUENCE dbo.MySeqCache1
AS INT
    START WITH 1
    INCREMENT BY 1
    MINVALUE 1
    MAXVALUE 9999
    NO CYCLE
    CACHE 1;
GO

Sekwencja nr 2:

CREATE SEQUENCE dbo.MySeqNoCache
AS INT
    START WITH 1
    INCREMENT BY 1
    MINVALUE 1
    MAXVALUE 9999
    NO CYCLE
    NO CACHE;
GO

Czy jest jakaś różnica między nimi? Czy będą się zachowywać inaczej, gdy będą używane w środowisku SQL Server 2012+?

sql-server sql-server-2012 sequence

— marc_s
źródło

Trudno jest udzielić ostatecznej odpowiedzi na to pytanie, dopóki nie znajdziesz różnicy. Nie znalazłem żadnego, ale to nie znaczy, że nie ma różnicy, tylko że nie widziałem go w testach, które wykonałem.

Łatwy test dotyczy wydajności. Pobranie kolejnej wartości w pętli lub użycie tabeli liczb jako źródła do generowania wielu wartości jednocześnie. W moich testach nie było różnicy w wydajności między brakiem pamięci podręcznej a pamięcią podręczną o wartości 1, ale zauważono znaczną poprawę wydajności przy użyciu pamięci podręcznej o wartości 2.

Oto kod, którego użyłem do przetestowania wydajności:

declare @D datetime = getdate();

declare @I int = 0;
while @I < 9999
  select @I = next value for dbo.S;

select datediff(millisecond, @D, getdate());

Wynik:

Cache        Time(ms)
------------ --------
NO CACHE     1200
1            1200
2             600
1000           70

Aby zagłębić się nieco głębiej, skorzystałem z rozszerzonych zdarzeń sqlserver.metadata_persist_last_value_for_sequencei sqlserver.lock_acquiredsprawdziłem, czy istnieje coś innego w utrzymywaniu wartości w tabeli systemowej.

Użyłem tego kodu, aby sprawdzić brak pamięci podręcznej i rozmiar pamięci podręcznej 1 i 4.

DECLARE @S NVARCHAR(max) = '
CREATE EVENT SESSION SeqCache ON SERVER 
ADD EVENT sqlserver.lock_acquired(
    WHERE (sqlserver.session_id=({SESSIONID}))),
ADD EVENT sqlserver.metadata_persist_last_value_for_sequence(
    WHERE (sqlserver.session_id=({SESSIONID}))) 
ADD TARGET package0.event_file(SET filename=N''d:\SeqCache'');';

SET @S = REPLACE(@S, '{SESSIONID}', CAST(@@SPID AS NVARCHAR(max)));

EXEC (@S);

GO

CREATE SEQUENCE dbo.S
AS INT
    START WITH 1
    INCREMENT BY 1
    MINVALUE 1
    MAXVALUE 9999
    NO CYCLE
    NO CACHE;
--    CACHE 1;
--    CACHE 4;

GO

ALTER EVENT SESSION SeqCache ON SERVER STATE = START;

GO

DECLARE @I INT = 0;
WHILE @I < 10
  SELECT @I = NEXT VALUE FOR dbo.S;

GO

ALTER EVENT SESSION SeqCache ON SERVER STATE = STOP;
DROP EVENT SESSION SeqCache ON SERVER;
DROP SEQUENCE dbo.S;

Nie ma różnicy w wynikach dla użycia pamięci podręcznej i pamięci podręcznej 1.

Przykładowe dane wyjściowe:

name                                      persisted_value mode
----------------------------------------- --------------- -----
lock_acquired                             NULL            SCH_S
lock_acquired                             NULL            IX
lock_acquired                             NULL            U
metadata_persist_last_value_for_sequence  1               NULL
lock_acquired                             NULL            SCH_S
lock_acquired                             NULL            IX
lock_acquired                             NULL            U
metadata_persist_last_value_for_sequence  2               NULL
lock_acquired                             NULL            SCH_S
lock_acquired                             NULL            IX
lock_acquired                             NULL            U
metadata_persist_last_value_for_sequence  3               NULL

Podczas korzystania z pamięci podręcznej 4.

name                                      persisted_value mode
----------------------------------------- --------------- -----
lock_acquired                             NULL            SCH_S
lock_acquired                             NULL            IX
lock_acquired                             NULL            U
metadata_persist_last_value_for_sequence  4               NULL
lock_acquired                             NULL            SCH_S
lock_acquired                             NULL            SCH_S
lock_acquired                             NULL            SCH_S
lock_acquired                             NULL            SCH_S
lock_acquired                             NULL            IX
lock_acquired                             NULL            U
metadata_persist_last_value_for_sequence  8               NULL

SCH_SZamka odbywa się, gdy potrzebna jest wartość. A kiedy pamięć podręczna się wyczerpie, następuje a IXi Ublokada, a na koniec zdarzenie metadata_persist_last_value_for_sequencejest uruchamiane.

Dlatego nie powinno być różnicy między brakiem pamięci podręcznej a pamięcią podręczną 1, jeśli chodzi o potencjalną utratę wartości podczas nieoczekiwanego zamknięcia programu SQL Server.

Wreszcie zauważyłem coś w zakładce Wiadomość w SSMS podczas tworzenia sekwencji z pamięcią podręczną 1.

Rozmiar pamięci podręcznej dla obiektu sekwencji „dbo.S” został ustawiony na NO CACHE.

Tak więc SQL Server uważa, że nie ma różnicy i tak mi mówi. W sys.sequenceskolumnie jest jednak różnica cache_size. Jest NULL dla braku pamięci podręcznej i 1 dla pamięci podręcznej 1.

— Mikael Eriksson
źródło