问题描述

CREATE SEQUENCE 有 CACHE 选项

MSDN 定义它作为

[ CACHE [<常量>] |无缓存]

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

我知道它通过避免从磁盘 IO 读取并在内存中维护一些有助于可靠地生成序列中的下一个数字的信息来提高性能，但我无法想象缓存的简单内存表示会是什么样子示例中的 MSDN 描述.

谁能解释一下缓存如何处理这个序列

CREATE SEQUENCE s作为 INT从 0 开始增加 25缓存 5

描述当**执行以下每个语句时缓存内存将保存的内容:

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

文档中的这一段非常有帮助:

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

所以，在你的场景中

CREATE SEQUENCE s作为 INT从 0 开始增加 25缓存 5

您将在内存中拥有 0、25、50、75 和 100，并且您将只有一个 I/O 写入磁盘:100.

文档中解释的您可能遇到的问题是，如果服务器出现故障并且您没有使用所有 5 个项目，下次您请求一个值时，您将得到 125.

CREATE SEQUENCE has CACHE option

MSDN defines it as

[ 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.

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.

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

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.

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.

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.