ip跳转跟踪命令_在跟踪命令（-T272）的帮助下学习避免身份跳转问题（IDENTITY_CACHE）

admin管理员组

文章数量:1530085

ip跳转跟踪命令

In this article, we will examine the Identity Cache feature and the identity jump issue. Sometimes we see that identity jumps by some specific or random value in the auto-incremental columns, which is known as the identity jump issue. Usually, it occurs when the SQL Server instance is being forced to restart. This skipped gap is particularly depending on the data type of the column, and most of the time, it is possible that column type can be INT, BIGINT, or Numeric.

在本文中，我们将研究身份缓存功能和身份跳转问题。 有时，我们会在自动增量列中看到身份跳跃某个特定值或随机值，这称为身份跳跃问题。 通常，它在强制重新启动SQL Server实例时发生。 跳过的间隙特别取决于列的数据类型，并且在大多数情况下，列类型可以是INT，BIGINT或Numeric。

In the release of SQL Server 2012, Microsoft has introduced a feature of Identity Cache. The identity jump doesn’t cause any issue neither with the database nor the running tasks; however, this identity value gap is not acceptable in some of the business-oriented scenarios. This Identity counter values of the columns are stored in the system table separately, which is known as sys.identity_columns DMV. This reference object sys.identity_columns does not rely on the transaction status, and it doesn’t matter that the transaction on the user table is committed or rolled back. Now, in case this user transaction is being rolled back, we will find that the identity value has been skipped in the sys.identity_columns table. The reason being is that the value of the Identity column value does not get rolled back. Apart from the above scenario, there could be various reasons or situations where these issues of the skip of the Identity value will be seen, which will be small in number and random. However, when there is an issue of Identity value jump, the amount of the skip value will be prominent and precise in number, depending upon the column data type, which is easy to identify.

在SQL Server 2012发行版中，Microsoft引入了身份缓存的功能。 身份跳转不会对数据库或正在运行的任务造成任何问题； 但是，在某些面向业务的方案中，此标识值差距是不可接受的。 列的此标识计数器值分别存储在系统表中，称为sys .identity_columns DMV。 该参考对象sys .identity_columns不依赖于事务状态，并且用户表上的事务已提交或回滚也无关紧要。 现在，如果此用户事务被回滚，我们将发现sys .identity_columns表中的标识值已被跳过。 原因是Identity列值的值不会回滚。 除上述情况外，可能还有各种原因或情况，将看到身份值跳过的这些问题，这些问题的数量很少且是随机的。 但是，当出现标识值跳转问题时，跳转值的数量将突出且数量精确，具体取决于易于识别的列数据类型。

SQL Server is using a different cache size for the various data type of identity columns. The cache size of the INT data type column is 1000, and the Cache size of the BIGINT or Numeric data typed column is 10000. For example, we have a sample table named by sales.OrderTracking with identity column named OrderTrackingID and INT data type.

SQL Server对身份列的各种数据类型使用不同的缓存大小。 INT数据类型列的缓存大小为1000，而BIGINT或Numeric数据类型列的缓存大小为10000。例如，我们有一个由sales.OrderTracking命名的示例表，其标识列名为OrderTrackingID和INT数据类型。

SELECT * 
FROM sales.OrderTracking 
ORDER BY OrderTrackingID DESC

Here, the total number of records is 188790, and in the last inserted record’s identity value is 188790.

此处，记录总数为188790，最后插入的记录的标识值为188790。

借助DMV（sys.identity_columns）验证当前身份值 (Verify the current identity value with the help of DMV (sys.identity_columns))

Below T-SQL statement returns the scope_identity for the table.

下面的T-SQL语句返回表的scope_identity。

SELECT OBJECT_NAME(object_id) AS TableName, 
  name AS ColumnName, 
  TYPE_NAME(system_type_id) AS DataType, 
  last_value
FROM sys.identity_columns ic
WHERE OBJECT_NAME(object_id) = 'OrderTracking'
GO

在强制重启之前在表中插入新行 (Inserting a new row in the table before force restart)

INSERT INTO sales.OrderTracking(SalesOrderID, CarrierTrackingNumber, TrackingEventID, EventDetails, EventDateTime)
VALUES(45681, '481CF8D-1C91-4ACC-9ABC-74', 6, 'Order has been delivered to its final destination', '2012-02-14 09:00:00.0000000')
(1 row affected)

With the insertion of one new row, the value of the identity will become 188791, and the next identity value should be 188792. Now, in case of a forceful reboot of the SQL Server Service with the help of Task Manager, the value of identity has jumped to a new value of 189790 for the column OrderTracking.

插入一个新行后，标识的值将变为188791，下一个标识值应为188792。现在，如果在任务管理器的帮助下强制重新启动SQL Server服务，则标识的值已将OrderTracking列的新值提高到189790 。

SELECT OBJECT_NAME(object_id) AS TableName, 
  name AS ColumnName, 
  TYPE_NAME(system_type_id) AS DataType, 
  last_value
FROM sys.identity_columns ic
WHERE OBJECT_NAME(object_id) = 'OrderTracking'
GO

We can see here that the Identity counter has jumped by 999 for a current stat and with 1000 over the last transaction on the table. The identity jump value depends on the last insert operation over the table. For example, we have inserted ten rows in the last process then the identity jump will be 990 after the force restart of SQL Server.

我们在这里可以看到，对于当前状态，身份计数器已跃升999，而在表的最后一个事务中跃迁了1000。 标识跳转值取决于对表的最后一次插入操作。 例如，我们在最后一个过程中插入了十行，那么在强制重启SQL Server之后，标识跳转将为990。

Now, let us insert 1 row again in the same table and verify the newly added row in the table.

现在，让我们在同一张表中再次插入1行，并验证表中新添加的行。

INSERT INTO sales.OrderTracking(SalesOrderID, CarrierTrackingNumber, TrackingEventID, EventDetails, EventDateTime)
VALUES(45681, '481CF8D-1C91-4ACC-9ABC-74', 6, 'Order has been delivered to its final destination', '2012-02-14 09:00:00.0000000')
(1 row affected)

In here, we can see that the new row inserted has got the identity value as 189791.

在这里，我们可以看到插入的新行的标识值为189791。

Before restarting the SQL Server instance, consecutive values of identity was being generated without any significant interruption. However, after the SQL Server service was restarted, we can see a gap in the incrementing Identity value, which indicates SQL Server is caching the value of identity columns and reseeding the column after the process of force restart takes place. To avoid this circumstance, we can choose any one of below three choices:

在重新启动SQL Server实例之前，将连续生成标识的值，而不会造成任何重大中断。 但是，在重新启动SQL Server服务之后，我们可以看到递增的Identity值中存在间隙，这表明SQL Server正在缓存Identity列的值，并在强制重新启动过程发生后重新播种该列。 为了避免这种情况，我们可以选择以下三个选项之一：

1. Disable the IDENTITY CACHE option at the database level
   在数据库级别禁用IDENTITY CACHE选项
2. Disable the IDENTITY CACHE at the server level with the help of Trace Flag
   借助跟踪标志在服务器级别禁用IDENTITY CACHE
3. Use a sequence with the NOCACHE option in replacement of the Identity option with the column
   将序列与NOCACHE选项一起使用，以用列替换Identity选项

在数据库级别禁用IDENTITY CACHE选项 (Disable the IDENTITY CACHE option at the database level)

The default configuration value of IDENTITY CACHE will be set as ‘ON’ for each user database level in SQL Server. Fundamentally IDENTITY CACHE is a feature to boost the performance of the insert operation. However, it could be disabled at the database level to avoid the identity gap in the column of the tables.

对于SQL Server中的每个用户数据库级别，IDENTITY CACHE的默认配置值将设置为“ ON”。 从根本上讲，身份缓存是一项功能，可以提高插入操作的性能。 但是，可以在数据库级别将其禁用，以避免表列中的标识差距。

USE DatabaseName
GO
ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE = OFF
GO

在SQL Server实例级别禁用IDENTITY CACHE (Disable the IDENTITY CACHE at SQL Server instance level)

IDENTITY CACHE can be disabled at SQL Server instance level with the help of ‘Trace flag 272’. We can add this TRACE flag in the start-up parameter to avoid executing it manually at the time of the start-up of the SQL Server instance.

可以在“跟踪标记272”的帮助下在SQL Server实例级别禁用IDENTITY CACHE。 我们可以在启动参数中添加此TRACE标志，以避免在SQL Server实例启动时手动执行它。

Open the property of SQL Server Services in SQL Server Configuration Manager > Properties > Startup Parameters > Add as shown below.

在“ SQL Server配置管理器” >“ 属性” >“ 启动参数” >“ 添加 ”中打开“ SQL Server服务”的 属性 ，如下所示。

在启动参数中添加TRACE FLAG (Adding a TRACE FLAG in Startup Parameters)

Here, we are adding a ‘TRACE flag 272’ in SQL Server instance property using -T272 in the start-up parameter. SQL Server service needs to be restarted for changes to take place in the start-up parameter.

在这里，我们在启动参数中使用-T272在SQL Server实例属性中添加“ TRACE标志272”。 需要重新启动SQL Server服务，以便在启动参数中进行更改。

重新启动SQL Server实例服务以应用更改 (Restart SQL Server Instance service to apply the changes)

The steps mentioned above will disable the IDENTITY CACHE at the server level for each existing database as well as the database being created. After applying the changes in the start-up parameter, users will be asked with the below prompt to restart the SQL Server instance.

上面提到的步骤将在服务器级别上为每个现有数据库以及正在创建的数据库禁用IDENTITY CACHE。 在启动参数中应用更改后，将要求用户使用以下提示重新启动SQL Server实例。

带有NOCACHE选项的SEQUENCE (SEQUENCE with NOCACHE option)

SEQUENCE is a user-defined incrementing object in the SQL Server database, which produces an integer incremental identity or sequence, according to the schema definition and specification. A SEQUENCE can be used as an artificially incremented Identity property for a column of the table. It works as a sequence generator, which was introduced in SQL Server 2012. We can use a sequence with the NOCACHE option with the primary key as a substitution of IDENTITY() to avoid the identity jump issue with the primary key.

SEQUENCE是SQL Server数据库中的用户定义的增量对象，根据架构定义和规范，该对象将生成整数增量标识或序列。 SEQUENCE可以用作表列的人为增加的Identity属性。 它用作SQL Server 2012中引入的序列生成器。我们可以将带有NOCACHE选项的序列与主键一起用作IDENTITY（）的替代，以避免主键出现身份跳转问题。

用NOCACHE创建一个SEQUENCE (Creating a SEQUENCE with NOCACHE)

CREATE SEQUENCE Seq_OrderTracking
AS BIGINT
START WITH 1
INCREMENT BY 1
MINVALUE 0
NO MAXVALUE
NO CACHE

Here, we have a sequence to generate a unique and incremental value generation option that starts from 1 and undefined maximum value. If users want to use a sequence with the table, then the table should be specified without the IDENTITY() parameter on the column, and the sequence object will be used in the INSERT statement when inserting a new row into the table.

在这里，我们有一个序列来生成一个唯一的增量值生成选项，该选项从1开始且未定义最大值。 如果用户要对表使用序列，则应在表上指定该列时不带IDENTITY（）参数，并且在向表中插入新行时将在INSERT语句中使用序列对象。

For example:

例如：

INSERT INTO sales.OrderTracking(OrderTrackingID, SalesOrderID, CarrierTrackingNumber, TrackingEventID, EventDetails, EventDateTime)
VALUES(NEXT VALUE FOR Seq_OrderTracking, 45681, '481CF8D-1C91-4ACC-9ABC-74', 6, 'Order has been delivered to its final destination', '2012-02-14 09:00:00.0000000')

NEXT VALUE FOR sequence command increments the counter and generates a new value for the object. Users can perform the counter reseed and kind of commands with the sequence using different sequence commands.

NEXT VALUE FOR序列命令递增计数器并为该对象生成一个新值。 用户可以使用不同的序列命令按序列执行计数器重新设定种子和命令种类。

This SEQUENCE solution resolves your identity jump issue with the identity columns in the SQL Server. However, you can approach this when the user is designing the database for the new product. For the existing system, this approach is not recommended because of many dependent object changes required to be done.

此SEQUENCE解决方案使用SQL Server中的标识列解决了您的标识跳转问题。 但是，当用户为新产品设计数据库时，可以采用这种方法。 对于现有系统，不建议使用此方法，因为需要进行许多相关的对象更改。

结论 (Conclusion)

Usually, the identity column is being used as the dark column, and that can be used to refer to the other tables (Primary – Foreign Key reference). There are numerous reasons to see the gap in the incremental sequence. It is not a matter of worry unless significant differences are found in the identity columns repetitively repetitive or randomly. When a big identity gap is found in the identity column, the first action that should be taken is to check the IDENTITY CACHE option for that particular database before proceeding with any further investigation.

通常，标识列用作暗列，并且可以用来引用其他表（主-外键引用）。 有很多原因可以看到增量序列中的间隔。 除非在身份列中重复地或随机地找到显着差异，否则不必担心。 如果在“标识”列中发现较大的标识差距，则应采取的第一个操作是在继续进行任何进一步调查之前，检查该特定数据库的“标识缓存”选项。

To resolve this issue for future databases, disabling the IDENTITY CACHE at the server level is recommended by adding the ‘Trace flag’ in the start-up parameter of the SQL Server Service. Ideally, the database administrator will have the number of Pre and Post activity list to perform on the new SQL Server setup. Fundamentally this ‘Trace flag’ should be included in the database administrators checklist for the Server Migration or Tech-Refresh kind of activities when the users won’t IDENTITY CACHE feature.

若要解决此问题以用于将来的数据库，建议通过在SQL Server服务的启动参数中添加“跟踪标志”来禁用服务器级别的IDENTITY CACHE。 理想情况下，数据库管理员将具有在新SQL Server安装程序上执行的Pre和Post活动列表的数量。 从根本上讲，当用户不会使用IDENTITY CACHE功能时，对于服务器迁移或技术更新这类活动，此“跟踪标志”应包括在数据库管理员清单中。

翻译自: https://www.sqlshack/learn-to-avoid-an-identity-jump-issue-identity_cache-with-the-help-of-trace-command-t272/

ip跳转跟踪命令

本文标签： 跳转命令身份ipIDENTITYCACHE