Enable identity cache feature in SQL Server 2017

Enable identity cache feature in SQL Server 2017

One type of contribution and appreciation of a developer (data warehouse) seems to be having a surplus efficiency in identifying surrogate keys. Go through on the dimension tables and facts to fetch the details on surrogate keys. Why more surrogate keys needed? It’s a tough competition but for defining abilities like table relationship, index creation & maintenance and constraints.

This surrogate key is generated as well as incremented using SQL Server identity property. But, there are more difficulties in generating keys in versions like 2017 SQL Server since it modularly creates a gap in identity values. It’s a big challenge now to overcome these gaps. This might be a hidden reason for some causes; identifying the solution for gap creation is a one strategized method. Let’s see the solution to makeover it:

How the gaps formed in identity values?

In SQL Server 2016, there was a repeated check on SQL Server shutdown problems, corruption in database and identity values generation etc. Memory cache gives a big hand to monitor all these problems immediately.

Sample table creation:

To save products namely “ApexSQL”, its must to create a table whereas it APexSQL has a free license for maintaining 6 products. So, execute below-scripts for table creation [dbo].[ApexSQL_Products].

Note: As said, identity property will be used by surrogate keys. The surrogate key here is [ProductID].

Code:

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];

Sample table populate:

Here, we are going to populate 3 ApexSQL a product from 6 is scripted below:

Code:

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

SQL Server Shutdown:

You can do checks on a database by shutting down the SQL Server:

Code:

SHUTDOWN WITH NOWAIT;

Products remaining insertion:

Till now, we have processed for 3 ApexSQL products so, let us add remaining products in the table.

Code:

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

Once if you run on the above script, the remaining product will be inserted.

Note: If you haven’t expected a SQL Server shutdown, it will affect by jumping the values of identity from 3 to 1002. So, there is no chance to see the middle of the content because the count goes directly from 3 to 1002. Stop the sequence of a shutdown immediately. It’s a warning right!

In another case, after shutdown, it immediately starts from the count 1000. Trying it by recreating the table but this time it starts from 1000000. If you check the count 2 to 4, look keenly the count can be started by skipping middle again from 1000002 – 1001001.

Here’s starts the solution:

One of the features from SQL Server 2016 has been added in the 2017 version also. All credit goes to database-scoped configuration! The one such feature is called “IDENTITY_CACHE” which disables and enables the identity value caches. This will help to stop creating the gap between identity values. The default option shows the SQL Server 2017 has cached.

Making an account of database-scoped configuration, its performance goes wide into SSMS directly by highlighting parameter sniffing and Max DOP. Go to database properties and then choose an option. Select database scored configuration, therefore, you can configure IDENTITY_CACHE.

But, you can configure IDENTITY_CACHE only in command of T-SQL. Here’s the command to disable cache of identity in 2017 version.

Code:

ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE = OFF
GO

There is no need of restarting the SQL Server 2017 whenever you disable or enable the identity cache. By now, cross-check the gap in-between identity values; you won’t see the gaps again.

You will be known that identity cache solves the gap formed in-between values of identity. What about shutdown and corruption in the database? Don’t come to the conclusion that gaps are the reason for shutdown and database corruption.

There may be other reasons like:

Rolling back of identity values
Rolling back of transactions
Failure while backing up the statement “Insert”

Let us go into deep:

Try by non-clustering the table index by executing the script:

Code:

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 you try by inserting the ApexSQL product it gives a warning message like “You are violating the rules”.

Code:

Msg 2601, Level 14, State 1, Line 19

Cannot insert a 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.

Here, you can try to add 1 at the ApexSQL product so that the INSERT script statement will give you good results. By doing this, the surrogate key will be jumping from 6 to 8. Not like before! From now, it won’t jump from 6 to 1000.

Hence, identity cache plays a major role in avoiding gaps in-between identity values. But still, you can see the minor area of the jump. Hope, this article will reduce your problem!

Enable identity cache feature in SQL Server 2017

More resources from bhawanisingh

Share this resource