使用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.
