SQL 2012 Hosting :: SQL Server 2012 Sequence Internal

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

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

What will be returned are

01 object_id = 581577110
02 id          name         nsid        nsclass status      type pid         pclass intprop     created                 modified                status2
03 ----------- ------------ ----------- ------- ----------- ---- ----------- ------ ----------- ----------------------- ----------------------- -----------
04 581577110   mySequence   1           0       512         SO   0           1      0           2011-11-01 01:17:36.507 2011-11-01 01:17:36.507 0
05
06 (1 row(s) affected)
07
08 valclass objid       subobjid    valnum      value
09 -------- ----------- ----------- ----------- --------------------------------------------------------------------------------------
10 63       581577110   0           4991        0x000000000000008001000000000000000000000000000080FFFFFFFFFFFFFF7F000000000000008001
11
12 (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

01 use test
02 set nocount on
03 if object_id('a') is not null
04 drop table a
05 if object_id('seq1') is not null
06 drop sequence seq1
07 go
08 create table a (id bigint identity(1,1))
09 go
10 declare @i int, @d datetime2(3), @j int
11 select @i = 0, @d = GETDATE()
12 while @i < 2000000
13 begin
14 begin transaction
15 insert into a default values
16 rollback
17 select @i = @i + 1
18 end
19 select 'Identity', DATEDIFF(millisecond, @d, getdate()) Milliseconds
20 go
21 create sequence seq1 no cache
22 go
23 declare @i int, @d datetime2(3), @j bigint
24 select @i = 0, @d = GETDATE()
25 while @i < 2000000
26 begin
27 select @j = next value for seq1
28 select @i = @i + 1
29 end
30 select 'Sequence Without Cache', DATEDIFF(millisecond, @d, getdate()) Milliseconds
31 go
32 drop sequence seq1
33 go
34 create sequence seq1 cache
35 go
36 declare @i int, @d datetime2(3), @j bigint
37 select @i = 0, @d = GETDATE()
38 while @i < 2000000
39 begin
40 select @j = next value for seq1
41 select @i = @i + 1
42 end
43 select 'Sequence With Cache 50', DATEDIFF(millisecond, @d, getdate()) Milliseconds
44 go
45 drop sequence seq1
46 go
47 create sequence seq1 cache 500
48 go
49 declare @i int, @d datetime2(3), @j bigint
50 select @i = 0, @d = GETDATE()
51 while @i < 2000000
52 begin
53 select @j = next value for seq1
54 select @i = @i + 1
55 end
56 select 'Sequence With Cache 500', DATEDIFF(millisecond, @d, getdate()) Milliseconds
57 go
58 drop sequence seq1
59 go
60 create sequence seq1 cache 5000
61 go
62 declare @i int, @d datetime2(3), @j bigint
63 select @i = 0, @d = GETDATE()
64 while @i < 2000000
65 begin
66 select @j = next value for seq1
67 select @i = @i + 1
68 end
69 select 'Sequence With Cache 5000', DATEDIFF(millisecond, @d, getdate()) Milliseconds
70 go
71 declare @i int, @d datetime2(3), @j bigint
72 select @i = 0, @d = GETDATE()
73 while @i < 2000000
74 begin
75 select @i = @i + 1
76 end
77 select 'Just Loop', DATEDIFF(millisecond, @d, getdate()) Milliseconds
78 go

The result for generating 2 million values:

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.