失眠网 > sql查询禁用缓存_如何在SQL Server 中启用和禁用身份缓存

sql查询禁用缓存_如何在SQL Server 中启用和禁用身份缓存

时间：2018-07-18 02:49:30

sql查询禁用缓存

Every data warehouse developer is likely to appreciate the significance of having surrogate keys as part of derived fields in your facts and dimension tables. Surrogate keys make it easy to define constraints, create and maintain indexes, as well as define relationships between tables. This is where the Identity property in SQL Server becomes very useful because it allows us to automatically generate and increment our surrogate key values in data warehouse tables. Unfortunately, the generating and incrementing of surrogate keys in versions of SQL Server prior to SQL Server was at times challenging and inconsistent by causing huge gaps between identity values. In this article, we take a look at one improvement made in SQL Server to reduce the creation of gaps between identity values.

每个数据仓库开发人员都可能会意识到将替代键作为事实和维度表中派生字段的一部分的重要性。代理键使定义约束，创建和维护索引以及定义表之间的关系变得容易。这是SQL Server中Identity属性变得非常有用的地方，因为它允许我们在数据仓库表中自动生成和增加代理键值。不幸的是，在SQL Server 之前的版本中，在SQL Server版本中生成和增加代理密钥有时会造成身份值之间的巨大差距，因此充满挑战且不一致。在本文中，我们看一下SQL Server 中所做的一项改进，以减少标识值之间的间隙的创建。

问题 (Problem)

Because versions of SQL Server prior to SQL Server used a memory cache to keep track of identity values to generate, database corruption or unexpected shutdowns of SQL Server instances led to the creation of gaps between identity values. In order to demonstrate the issue at hand, we make use of the following steps:

由于SQL Server 之前SQL Server版本使用内存缓存来跟踪要生成的标识值，因此数据库损坏或SQL Server实例的意外关闭会导致标识值之间产生间隙。为了演示当前问题，我们使用以下步骤：

Step 1: Create the sample table

步骤1：创建示例表

In this step, we create a table that will store a list of ApexSQL products available for free – a as at the time of writing this article, ApexSQL had 6 products licensed for free.Script 1shows a create table statement for the[dbo].[ApexSQL_Products]table that will be used to store our product list. Furthermore, the script also indicates that[ProductID]is our surrogate key that makes use of the Identity property.

在此步骤中，我们创建一个表，该表将存储免费提供的ApexSQL产品列表–在撰写本文时，ApexSQL拥有6个免费许可的产品。脚本1显示了[dbo]。[ApexSQL_Products]表的创建表语句，该语句将用于存储我们的产品列表。此外，脚本还指示[ProductID]是我们的代理密钥，它使用了Identity属性。

CREATE TABLE [dbo].[ApexSQL_Products]([ProductID]INT IDENTITY(1, 1) NOT NULL,[ProductName][VARCHAR](50) NOT NULL,[DateInserted] DATETIME2 DEFAULT(GETDATE()) NOT NULL)ON [PRIMARY];

Step 2: Populate the sample table

步骤2：填充示例表

The next step involves populating the newly created table with 3 out of the 6 ApexSQL products as shown inScript 2.

下一步涉及用6个ApexSQL产品中的3个填充新创建的表，如脚本2所示。

INSERT INTO [dbo].[ApexSQL_Products]([ProductName])VALUES('ApexSQL Compare'), ('ApexSQL Complete'), ('ApexSQL Refactor');

A preview of our[dbo].[ApexSQL_Products]table indicates that the execution ofScript 2was successful as the 3 inserted products appear in the table as shown inFigure 1.

我们的[dbo]。[ApexSQL_Products]表的预览显示，由于插入的3个产品出现在表中，如图2所示，脚本2的执行成功。

Step 3: Shut down SQL Server

步骤3：关闭SQL Server

Having inserted 3 of the 6 ApexSQL products licensed for free, we forcibly shut down our SQL Server instance without performing database checkpoints by using the command shown inScript 3.

在插入了免费许可的6种ApexSQL产品中的3种之后，我们通过使用脚本3中显示的命令来强制关闭SQL Server实例，而无需执行数据库检查点。

SHUTDOWN WITH NOWAIT;

Step 4: Resume table inserts

步骤4：恢复表插入

In this step, we restart the SQL Server instance that we stopped in step 3 and we then do an insert of the remaining free ApexSQL products using the syntax shown inScript 4.

在此步骤中，我们重新启动在步骤3中停止SQL Server实例，然后使用脚本4中显示的语法插入剩余的免费ApexSQL产品。

INSERT INTO [dbo].[ApexSQL_Products]([ProductName])VALUES('ApexSQL Search'), ('ApexSQL Plan'), ('ApexSQL Propagate');

Following the execution ofScript 4, a preview of our[dbo].[ApexSQL_Products]table inFigure 2confirms that the additional products we successfully inserted. However, it can also be noticed that, following an unexpected shutdown of our SQL Server instance, the identity values allocated forProductIDjumps from 3 to 1002.

执行脚本4后，图2中[dbo]。[ApexSQL_Products]表的预览确认我们已成功插入其他产品。但是，还应该注意到，在意外关闭SQL Server实例之后，为ProductID分配的标识值从3跃升到1002。

It looks like whenever a SQL Server instance goes through an unexpected shutdown, the next identity value to be generated will be a 1000 more than the previously generated value. For instance, say we drop and recreate our[dbo].[ApexSQL_Products]table but this time around our identity starts at 1 000 000 and is incremented by 1, as soon as we complete steps 2-4, a latest preview of our table inFigure 3indicates that the value for the 4th row jumps from 1000002 to 1001001.

看起来，每当SQL Server实例意外关闭时，下一个要生成的标识值将比先前生成的值大1000。例如，假设我们删除并重新创建了[dbo]。[ApexSQL_Products]表，但这一次我们的标识从1 000 000开始，并在完成步骤2-4（表的最新预览）后立即递增1。在图3中表明，对于第 4行的值从跳转到1000002 1001001。

解 (Solution)

Database-scoped configurations introduced in SQL Server has luckily been extended in SQL Server to include IDENTITY_CACHE option for enabling and disabling of caching of identity values. Thus, ensuring that database corruption or unexpected SQL Server shutdown does not create gaps between last generated identity value and the next identity value. By default, caching of identity values is enabled in SQL Server .

幸运的是，SQL Server 中引入的数据库范围配置已在SQL Server 中进行了扩展，以包括IDENTITY_CACHE选项，用于启用和禁用标识值的缓存。因此，确保数据库损坏或SQL Server意外关闭不会在上次生成的标识值和下一个标识值之间造成间隙。默认情况下，SQL Server 中启用了身份值缓存。

Whilst setting of database scoped configurations such as specifying Max DOP and Parameter Sniffing could be performed directly in SSMS by navigating to databaseOptions>Database Scoped Configurations(as shown inFigure 4), the setting of IDENTITY_CACHE option in SQL Server can only be done through T-SQL commands.

虽然可以通过导航到数据库选项>数据库范围配置（如图4所示）直接在SSMS中直接执行数据库范围配置的设置（例如指定Max DOP和参数嗅探），但只能在SQL Server 中完成IDENTITY_CACHE选项的设置通过T-SQL命令。

Script 5shows a T-SQL command for disabling identity cache in SQL Server

脚本5显示了用于在SQL Server 中禁用身份缓存的T-SQL命令

ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE = OFF GO

The enabling and disabling of identity cache occurs instantaneously without the need to restart the SQL Server service. As a result of successfully executingScript 5, when we recreate our target table and populate it according to steps 1-4, we end up with a surrogate key that doesn’t have gaps in between as shown inFigure 5.

身份高速缓存的启用和禁用是瞬间发生的，无需重新启动SQL Server服务。成功执行脚本5的结果是，当我们重新创建目标表并根据步骤1-4填充目标表时，最终得到一个替代键，两者之间没有间隙，如图5所示。

IDENTITY_CACHE并非灵丹妙药 (IDENTITY_CACHE not quite the silver bullet)

Although disabling of identity caching in SQL Server helps deal with gaps in identity values, it must be noted that database corruption and unexpected SQL Server shutdowns are not the only causes for gaps in identity values. Some other causes relate to batch INSERT statement failures that, despite the transaction being rolled back, the allocated identity value is never rolled back thus causing a gap.

尽管在SQL Server 中禁用身份缓存有助于解决身份值之间的差距，但必须注意的是，数据库损坏和意外SQL Server关闭不是造成身份值差距的唯一原因。其他一些原因与批处理INSERT语句失败有关，尽管事务已回滚，但分配的标识值从不回滚，从而导致间隔。

To demonstrate this issue, let’s add a unique non-clustered index to the table we created usingScript 1as shown inScript 6.

为了演示此问题，让我们向使用脚本1创建的表中添加唯一的非聚集索引，如脚本6所示。

CREATE UNIQUE NONCLUSTERED INDEX [NCI_IDX] ON [dbo].[ApexSQL_Products]([ProductName] ASC) WITH(PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];GO

If we try to insert productApexSQL Completeagain, we receive an error message citing a violation of our unique non-clustered index as shown in below:

如果我们尝试再次插入产品ApexSQL Complete，则会收到一条错误消息，指出违反了我们唯一的非聚集索引，如下所示：

Msg 2601, Level 14, State 1, Line 19Cannot insert duplicate key row in object 'dbo.ApexSQL_Products' with unique index 'NCI_IDX'. The duplicate key value is (ApexSQL Complete).The statement has been terminated

However, if we modify our insert script by adding 1 at the end of productApexSQL Complete, the INSERT statement successfully commits. Yet, if you have a look at the last identity value generated for our surrogate key, you will notice the jump from 6 to 8, as shown inFigure 6. Admittedly, the gap between last and next identity value is no longer a 1000 like we had when we unexpectedly shut down a server but it is still a jump.

但是，如果我们通过在产品ApexSQL Complete的末尾添加1来修改插入脚本，则INSERT语句将成功提交。但是，如果您查看为我们的代理密钥生成的最后一个身份值，您会注意到从6到8的跳转，如图6所示。诚然，最后一个标识值与下一个标识值之间的距离不再像我们意外关闭服务器时的距离那样为1000，但这仍然是一个跳跃。

摘要 (Summary)

The new IDENTITY_CACHE feature in SQL Server significantly improves the consistency of generating identity values for data-repository environments such as a SQL Server-based data warehouse. Yet, just because you have disabled the caching of identity values does not necessarily mean that you are not going to have gaps between your identity values as unsuccessful INSERT statements continue to create a jump between last and next identity values.

SQL Server 中的新IDENTITY_CACHE功能大大提高了为数据存储库环境（例如基于SQL Server的数据仓库）生成标识值的一致性。但是，仅仅因为您已禁用标识值的缓存，并不一定意味着您将不会在标识值之间造成缝隙，因为不成功的INSERT语句会继续在最后一个标识值和下一个标识值之间产生跳转。