Nov 182011
 

Sequence is a new feature introduced in SQL Server 2012. Unlike identity values, sequence is an independent object. It can be used in table definition as a default constraint on a column or referenced from a query including OVER clause. There are few DDLs and DMLs supporting sequences.

  • Create Sequence: create a sequence optionally with properties, returning type, starting value, interval, minimum value, maximum value, cycle, cache. If you don’t specify any properties, bigint will be used as returning data type.
  • Alter Sequence: Modify properties of sequence, returning type, starting value, interval, minimum value, maximum value, cycle, cache
  • Drop Sequence: Remove a sequence.
  • Next Value For: Get next value of a sequence.It cannot be called from functions.
  • sp_sequence_get_range: Get range of sequence.
  • sys.sequences: DMV for sequence.

Sequence Structre
When you create an sequence, a record will be added to sys.sysschobjs which host the name of the the sequence with properties such as creation date, last modify date etc. Another record will be added to sys.sysobjvalues which is to save the properties of a sequence. Please see the code snippet below and run is under admin mode

if object_id('mySequence') is not null
	drop sequence mySequence
create sequence mySequence;
declare @object_id int
select @object_id = object_id('mySequence')
print format(@object_id, 'object_id = 0')
select * from sys.sysschobjs where id = @object_id
select valclass,objid,subobjid,valnum,value from sys.sysobjvalues where objid = @object_id

What will be returned are

object_id = 581577110
id          name         nsid        nsclass status      type pid         pclass intprop     created                 modified                status2
----------- ------------ ----------- ------- ----------- ---- ----------- ------ ----------- ----------------------- ----------------------- -----------
581577110   mySequence   1           0       512         SO   0           1      0           2011-11-01 01:17:36.507 2011-11-01 01:17:36.507 0

(1 row(s) affected)

valclass objid       subobjid    valnum      value
-------- ----------- ----------- ----------- --------------------------------------------------------------------------------------
63       581577110   0           4991        0x000000000000008001000000000000000000000000000080FFFFFFFFFFFFFF7F000000000000008001

(1 row(s) affected)

The interpretations of the returning values are

  • Line 1: it’s an independent object. You can use meta data function, object_id(), object_name(), object_schema_name(), etc, to get names or ids.
  • Line 4: Object type is SO. Column intprop is the cache size. In this case the cache size is 0 which means use default size 50. Column status records whether or not the sequence value is cached.
  • Line 10: rest of the definition of the sequence, min value, max value, current value

How Sequence Works
No Cache defined: For each of NEXT VALUE FOR calls, SQL Server will apply a SCH_S lock on object mySequence to ensure there is no schema modification allowed while getting next value, then apply IX lock on sys.sysobjvalues table and U lock on the definition (line 10 above), modified the current value in value field, and finally release all locks. If there is no cache, every NEXT VALUE FOR call will perform an update to an system table. This update is not regular update. It uses update lock to lock the record instead of exclusive locks. This can increase the concurrency of system tables and also reduce the overhead of lock conversion (converting U lock to X lock, for more detail, see my locking and blocking series)
Cache are defined: When a cache is defined, SQL Server internally does NOT maintain a list of values, instead, it only keeps 2 values, one is the first value and another one is the last value. When NEXT VALUE FOR is called, the sequence number returned from cache. No further update to system table needed at this moment. When the cache is exhaused, the next value and the last value for the sequence are re-calculated, and then the last value will be saved in the sys.sysobjvalues table in the same way as explain in previous paragraph. When the server is shutdown, the current value of the sequence will be re-written to the this table but if the server is terminated, you will lose values from current value to the last value in the cache.
Default Cache: When neither cache nor no cache are specified, default cache is used. The size of it is 50.
sys.sp_sequence_get_range: This is the procedure get range of values. It calls an internal system procedure, sys.sp_sequence_get_range_internal. This procedure is very efficient. If cache is defined, range will be read from cache. If no cache is defined, performance of this procedure will be as quick executing NEXT VALUE FOR once.

Performance of Squence

use test
set nocount on
if object_id('a') is not null
	drop table a
if object_id('seq1') is not null
	drop sequence seq1
go
create table a (id bigint identity(1,1))
go
declare @i int, @d datetime2(3), @j int
select @i = 0, @d = GETDATE()
while @i < 2000000
begin
	begin transaction
	insert into a default values
	rollback 
	select @i = @i + 1
end
select 'Identity', DATEDIFF(millisecond, @d, getdate()) Milliseconds
go
create sequence seq1 no cache
go
declare @i int, @d datetime2(3), @j bigint
select @i = 0, @d = GETDATE()
while @i < 2000000
begin
	select @j = next value for seq1
	select @i = @i + 1
end
select 'Sequence Without Cache', DATEDIFF(millisecond, @d, getdate()) Milliseconds
go
drop sequence seq1
go
create sequence seq1 cache
go
declare @i int, @d datetime2(3), @j bigint
select @i = 0, @d = GETDATE()
while @i < 2000000
begin
	select @j = next value for seq1
	select @i = @i + 1
end
select 'Sequence With Cache 50', DATEDIFF(millisecond, @d, getdate()) Milliseconds
go
drop sequence seq1
go
create sequence seq1 cache 500
go
declare @i int, @d datetime2(3), @j bigint
select @i = 0, @d = GETDATE()
while @i < 2000000
begin
	select @j = next value for seq1
	select @i = @i + 1
end
select 'Sequence With Cache 500', DATEDIFF(millisecond, @d, getdate()) Milliseconds
go
drop sequence seq1
go
create sequence seq1 cache 5000
go
declare @i int, @d datetime2(3), @j bigint
select @i = 0, @d = GETDATE()
while @i < 2000000
begin
	select @j = next value for seq1
	select @i = @i + 1
end
select 'Sequence With Cache 5000', DATEDIFF(millisecond, @d, getdate()) Milliseconds
go
declare @i int, @d datetime2(3), @j bigint
select @i = 0, @d = GETDATE()
while @i < 2000000
begin
	select @i = @i + 1
end
select 'Just Loop', DATEDIFF(millisecond, @d, getdate()) Milliseconds
go

The result for generating 2 million values:
SequencePerformance
So the conclusion is that identity is 10 times slower than sequence with default cache size. You will not gain significant performance by defining very big cache size.

  4 Responses to “SQL Server 2012 Sequence Internal”

  1. I’m sorry but I don’t think this is a very good comparison between the two. your example of identity you actualy insert a row then roll it back, thats a lot of overhead that doesn’t happen as part of the sequence tests, where you only get the next sequence and do nothing with. It doesn’t surprise me in the least that its faster. Other than that I do like the article.

    • I fully understand what you say. There is only one way to get identity value. So the overhead of identity has to be counted. However, identity may not hvae overhead on sys.sysobjvalues. If we remove all overhead from both, those 2 will be esentially the same.

  2. About CACHE
    My tests show that after shutdown (restart from Conf. Manager) current sequence is not re-written to sys tables.

    For example:
    create sequence TestSeq cache 50

    SELECT NEXT VALUE FOR TestSeq
    1
    2
    3
    (restart)
    50
    51
    ….

    • That’s a great test. whenever a chunck of sequence generated, the last number is saved in the system table and current value is saved in memory. when system started next time, the current value i just lost and start over based on the value in the system table. Thanks for doing such great test.

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="">

(required)

(required)

C# | HTML | Plain Text | SQL | XHTML | XML | XSLT |

This site uses Akismet to reduce spam. Learn how your comment data is processed.