创建序列的缓存选项如何工作?

CREATE SEQUENCE has CACHE option

创建序列有缓存选项

MSDN defines it as

MSDN将它定义为

[ CACHE [<constant> ] | NO CACHE ]
Increases performance for applications that use sequence objects by minimizing the number of disk IOs that are required to generate sequence numbers. Defaults to CACHE. For example, if a cache size of 50 is chosen, SQL Server does not keep 50 individual values cached. It only caches the current value and the number of values left in the cache. This means that the amount of memory required to store the cache is always two instances of the data type of the sequence object.

通过最小化生成序列号所需的磁盘IOs数量，提高使用序列对象的应用程序的性能。默认缓存。例如，如果选择了50的缓存大小，SQL Server就不会缓存50个单独的值。它只缓存当前值和留在缓存中的值的数量。这意味着存储缓存所需的内存总是序列对象的数据类型的两个实例。

I understand it improves performance by avoiding reads from disk IO and maintaining some info in the memory that would help reliably generate the next number in the sequence, but I cannot imagine what a simple memory representation of the cache would look like for what the MSDN describes in the example.

我理解它提高了性能,避免读取从磁盘IO和维护一些信息在内存有助于可靠地生成序列中的下一个数字,但我无法想象一个简单的缓存的内存表示样子MSDN所描述的例子。

Can someone explain how would the cache work with this sequence

有人能解释一下缓存是如何处理这个序列的吗

CREATE SEQUENCE s
    AS INT
    START WITH 0  
    INCREMENT BY 25  
    CACHE 5

describing what the cache memory would hold when each of the following statements is executed independently:

描述当独立执行以下语句时，缓存内存将保存什么:

SELECT NEXT VALUE FOR s -- returns 0
SELECT NEXT VALUE FOR s -- returns 25
SELECT NEXT VALUE FOR s -- returns 50
SELECT NEXT VALUE FOR s -- returns 75
SELECT NEXT VALUE FOR s -- returns 100
SELECT NEXT VALUE FOR s -- returns 125

1 个解决方案

#1

This paragraph in the doc is very helpful:

《宣言》中的这一段非常有用:

For an example, a new sequence is created with a starting value of 1 and a cache size of 15. When the first value is needed, values 1 through 15 are made available from memory. The last cached value (15) is written to the system tables on the disk. When all 15 numbers are used, the next request (for number 16) will cause the cache to be allocated again. The new last cached value (30) will be written to the system tables.

例如，创建一个新的序列，初始值为1，缓存大小为15。当需要第一个值时，可以从内存中获取值1到15。最后的缓存值(15)被写到磁盘上的系统表中。当所有15个数字都被使用时，下一个请求(对于数字16)将导致再次分配缓存。新的最后缓存值(30)将被写入系统表。

So, in your scenario

所以,在你的场景

CREATE SEQUENCE s
    AS INT
    START WITH 0  
    INCREMENT BY 25  
    CACHE 5

You will have 0, 25, 50, 75 and 100 in Memory and you will get only one I/O write in disk: 100.

在内存中有0、25、50、75和100，在磁盘中只有一个I/O: 100。

The problem you could have, explained in the the doc, is if the server goes down and you haven't used all the 5 items, next time you ask for a value you'll get 125.

你可能会遇到的问题，在doc中解释，是如果服务器宕机，你没有使用所有的5项，下次你问一个值时，你会得到125。

#1