序列 - 無記憶體與記憶體 1

SQL Server 2012+ 中SEQUENCE聲明的使用NO CACHE和聲明的使用之間有什麼區別嗎？CACHE 1

序列#1：

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

序列#2：

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

兩者有什麼區別嗎？在 SQL Server 2012+ 環境中使用時它們的行為會有所不同嗎？

在您真正找到不同之處之前，很難對這個問題給出明確的答案。我沒有發現，但這並不意味著我沒有在我所做的測試中看到任何區別。

簡單的測試是性能。要麼在循環中獲取下一個值，要麼使用數字表作為源來一次生成多個值。在我的測試中，不使用記憶體和使用 1 值的記憶體在性能上沒有差異，但是使用 2 的記憶體有顯著的性能提升。

這是我用來測試性能的程式碼：

declare @D datetime = getdate();

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

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

結果：

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

為了更深入地探勘，我使用了擴展事件sqlserver.metadata_persist_last_value_for_sequence，並sqlserver.lock_acquired查看這些值在系統表中的持久性是否存在不同。

我使用這段程式碼來測試沒有記憶體和記憶體大小為 1 和 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;

不使用記憶體和記憶體為 1 的輸出沒有區別。

樣本輸出：

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

使用 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_S需要一個值時，鎖定就完成了。當記憶體用盡時，緊隨其後的是 aIX和 aU鎖，最後metadata_persist_last_value_for_sequence觸發事件。

因此，在 SQL Server 意外關閉時可能會失去值時，使用 no cache 和 cache 1 應該沒有區別。

最後，在使用記憶體 1 創建序列時，我注意到 SSMS 的“消息”選項卡中有一些內容。

序列對象“dbo.S”的記憶體大小已設置為 NO CACHE。

因此，SQL Server 認為沒有區別並告訴我。sys.sequences但是，列中存在差異cache_size。無記憶體時為 NULL，記憶體為 1 時為 1。

引用自：https://dba.stackexchange.com/questions/140074