sql-server – Sequence – NO CACHE vs CACHE 1

使用NO CACHE声明的SEQUENCE和使用SQL Server 2012中的CACHE 1声明的SEQUENCE之间有什么区别吗？

序列#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锁定.当缓存耗尽时,接着是IX和U锁,最后触发事件metadata_persist_last_value_for_sequence.

因此,在意外关闭SQL Server时可能会丢失值时,不使用缓存和缓存1之间应该没有区别.

最后,在创建带缓存1的序列时,我在SSMS的Message选项卡中注意到了一些内容.

The cache size for sequence object ‘dbo.S’ has been set to NO CACHE.

所以,SQL Server认为没有区别并告诉我.但是,cache_size列中的sys.sequences存在差异.没有缓存为NULL,缓存为1时为1.

（编辑：衡阳站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!