SQL Server 2012 Gotcha with Identity Column
-
We just ran across a gotcha with our SQL Server 2008 R2 to SQL Server 2012 migration.
One of the applications extensively uses identity columns to provide primary keys for its data rows. Unlike SQL Server 2008 R2, SQL Server 2012 identity seeds can appear to "jump" when a failover or shutdown occurs due to the way SQL Server 2012 now caches identity column values. The "missing" identity values appear to be a "new feature" 🙁 for SQL Server 2012:
http://msdn.microsoft.com/en-us/library/ms186775(v=sql.110).aspx
The above link states: "Consecutive values after server restart or other failures –SQL Server might cache identity values for performance reasons and some of the assigned values can be lost during a database failure or server restart. This can result in gaps in the identity value upon insert. If gaps are not acceptable then the application should use a sequence generator with the NOCACHE option or use their own mechanism to generate key values."
I presume the new sequence feature in SQL Server 2012 shares some of the database source code for identity column processing. The "create sequence" syntax allows you to turn off caching:
http://msdn.microsoft.com/en-us/library/ff878091.aspx
I'm trying to help a Developer's reports stay "pretty" (i.e., avoid any missing key values) without causing a major rewrite of the application.
Can caching be turned off for identity values in SQL Server 2012?
-
Identities have never been gap-less. Why is having no gaps in what is probably an artificial primary key (column without meaning) important?
If no gaps is a requirement, you can't use identities, 2012 or any other version.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
>>Why is having no gaps in what is probably an artificial primary key (column without meaning) important?
To DBAs, this isn't important, because *we* know that identity columns are arbitrarily assigned. However, the users of the report in question are accustomed to seeing mostly consecutive values in the report column that displays the identify values.
With SQL Server 2008, apparently there were fewer gaps, or the jumps between the gaps were smaller. This issue came up for us today because an end user was freaking out about "missing" more than a thousand rows, which launched into a discussion about whether or not there was some database corruption, etc...
Probably what made the user excitement worse is that the "missing" rows came to light just after a database migration to a newer version of SQL Server. And, no, the users did not detect this issue in Development or Test. 🙁 Production was the first time it was noticed.
-
shew (11/14/2013)
>>Why is having no gaps in what is probably an artificial primary key (column without meaning) important?To DBAs, this isn't important, because *we* know that identity columns are arbitrarily assigned. However, the users of the report in question are accustomed to seeing mostly consecutive values in the report column that displays the identify values.
With SQL Server 2008, apparently there were fewer gaps, or the jumps between the gaps were smaller. This issue came up for us today because an end user was freaking out about "missing" more than a thousand rows, which launched into a discussion about whether or not there was some database corruption, etc...
Probably what made the user excitement worse is that the "missing" rows came to light just after a database migration to a newer version of SQL Server. And, no, the users did not detect this issue in Development or Test. 🙁 Production was the first time it was noticed.
This is why you never, EVER show surrogate key values to an end user!! 😎
Also, wasn't there a patch issued for this? I may be misremembering the stuff on the Connect item about it.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service -
shew (11/14/2013)
To DBAs, this isn't important, because *we* know that identity columns are arbitrarily assigned. However, the users of the report in question are accustomed to seeing mostly consecutive values in the report column that displays the identify values.Then the question becomes, why is a meaningless identity column shown in a report? If you need row numbers, use the ROW_NUMBER function to generate sequential numbers with no gaps. Surrogate keys shouldn't be exposed to users for exactly this reason.
With SQL Server 2008, apparently there were fewer gaps, or the jumps between the gaps were smaller. This issue came up for us today because an end user was freaking out about "missing" more than a thousand rows, which launched into a discussion about whether or not there was some database corruption, etc...
Gaps occur in SQL 2008 for different reasons to 2012, but no, they aren't always smaller or fewer, depends on how the data is inserted.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Gail,
Another concern is that we have two scheduled instance failovers a week so that our clusters can be patched during established time frames, which means that around 2,000 identity values could get "skipped" per week because the cached values will never get used. Over the course of the year, potentially 100,000 identity values would be skipped.
For tables with high "burn rates" for identity values, skipping a large number of values makes the users uncomfortable about using the available identify values even quicker than they have in the past. (Yeah, this is an application design thing, but a re-write is not an option for the moment.)
By the way, do you of any way to reduce the number of identity values cached? That could potentially improve the issue.
j
-
TheSQLGuru (11/14/2013)This is why you never, EVER show surrogate key values to an end user!! 😎
You won't have any argument on this from me. LOL However, this is an old, highly visible application that our DBAs cannot influence--our organization hosts the application's databases. We cannot control the application code because it is used and owned by an external party.
TheSQLGuru (11/14/2013)Also, wasn't there a patch issued for this? I may be misremembering the stuff on the Connect item about it.
We found a trace flag, -T272, that reverts the instance back to the SQL Server 2008 R2 functionality, but I'm not overly execited about using trace flags if we can avoid them, especially since we have several instances with the same "issue."
If there is a patch available, I would love to learn more about it.
-
To DBAs, this isn't important, because *we* know that identity columns are arbitrarily assigned. However, the users of the report in question are accustomed to seeing mostly consecutive values in the report column that displays the identify values.
This sounds like a job for row_number()
-
Nevyn (11/14/2013)
To DBAs, this isn't important, because *we* know that identity columns are arbitrarily assigned. However, the users of the report in question are accustomed to seeing mostly consecutive values in the report column that displays the identify values.
This sounds like a job for row_number()
That's an AWFULLY expensive operation to fix what is essentially a USER TRAINING issue (since the OP can't change the application)!!!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service -
I have exactly the same issues on my 2012 servers, Googled and found the following script (sorry can't remember where) - run as a startup proc to "reset" the identity seeds to the next sequential value- I have changed it slightly so that it works on database names with fullstops in them - another inherited problem..
CREATE PROCEDURE dbo.sp_FixSeeds2012AS
BEGIN
--foreach database
DECLARE @DatabaseName VARCHAR(255)
DECLARE DatabasesCursor CURSOR READ_ONLY FOR
SELECT name
FROM
sys.databases
WHERE
name NOT IN ('master', 'tempdb', 'model', 'msdb')
AND sys.databases.state_desc = 'online'
OPEN DatabasesCursor
FETCH NEXT FROM DatabasesCursor INTO @DatabaseName
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC ('USE [' + @DatabaseName +
']
--foreach identity column
DECLARE @tableName varchar(255)
DECLARE @columnName varchar(255)
DECLARE @schemaName varchar(255)
DECLARE IdentityColumnCursor CURSOR READ_ONLY
FOR
select TABLE_NAME , COLUMN_NAME, TABLE_SCHEMA
from INFORMATION_SCHEMA.COLUMNS
where COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, ''IsIdentity'') = 1
OPEN IdentityColumnCursor
FETCH NEXT FROM IdentityColumnCursor
INTO @tableName, @columnName, @schemaName
WHILE @@FETCH_STATUS = 0
BEGIN
print ''[' + @DatabaseName +
'].[''+@tableName+''].[''+@schemaName+''].[''+@columnName+'']''
EXEC (''declare @max-2 int = 0
select @max-2 = max(''+@columnName+'') from [' + @DatabaseName
+
'].[''+@schemaName+''].[''+@tableName+'']
if (@MAX IS NULL)
BEGIN
SET @max-2 = 0
END
DBCC CHECKIDENT(''''[' + @DatabaseName +
'].[''+@schemaName+''].[''+@tableName+'']'''',RESEED,@MAX)'')
FETCH NEXT FROM IdentityColumnCursor
INTO @tableName, @columnName, @schemaName
END
CLOSE IdentityColumnCursor
DEALLOCATE IdentityColumnCursor')
FETCH NEXT FROM DatabasesCursor INTO @DatabaseName
END
CLOSE DatabasesCursor
DEALLOCATE DatabasesCursor
END
GO
EXEC sp_configure 'show advanced options', 1 ;
GO
RECONFIGURE
GO
EXEC sp_configure 'scan for startup procs', 1 ;
GO
RECONFIGURE
GO
EXEC sp_procoption @ProcName = 'sp_FixSeeds2012'
, @OptionName = 'startup'
, @OptionValue = 'true'
GO
-
Some additional links on the subject for you:
http://stackoverflow.com/questions/13657461/odd-sql-server-2012-identity-issue
-
shew (11/14/2013)
Gail,Another concern is that we have two scheduled instance failovers a week so that our clusters can be patched during established time frames, which means that around 2,000 identity values could get "skipped" per week because the cached values will never get used. Over the course of the year, potentially 100,000 identity values would be skipped.
For tables with high "burn rates" for identity values, skipping a large number of values makes the users uncomfortable about using the available identify values even quicker than they have in the past. (Yeah, this is an application design thing, but a re-write is not an option for the moment.)
By the way, do you of any way to reduce the number of identity values cached? That could potentially improve the issue.
j
If you have scheduled failovers then you can avoid the gap issue by manually issuing a CHECKPOINT to each database before the failover.
/* IDENTITY GAPS */USE AdventureWorks2012;
CREATE TABLE MyTest (ID INT IDENTITY(1, 1), MyValue VARCHAR(10));
INSERT INTO MyTest VALUES ('')
GO 10
SELECT * FROM MyTest -- ID 1 to 10
-- RESTART THE SQL SERVICE USING SSMS
INSERT INTO MyTest VALUES ('')
GO 10
SELECT * FROM MyTest -- New IDs 1002 to 1011
DROP TABLE MyTest;
GO
/* NO IDENTITY GAPS */
USE AdventureWorks2012;
CREATE TABLE MyTest (ID INT IDENTITY(1, 1), MyValue VARCHAR(10));
INSERT INTO MyTest VALUES ('')
GO 10
SELECT * FROM MyTest -- ID 1 to 10
CHECKPOINT
-- RESTART THE SQL SERVICE USING SSMS
INSERT INTO MyTest VALUES ('')
GO 10
SELECT * FROM MyTest -- New IDs 11 to 20
DROP TABLE MyTest;
-
I tried the manual checkpoints before posting this thread. They did not work for our site. The gaps consistently occurred anyway.
-
I recently ran into this with a gap of 10,000 values. The table in question is used to assign a "session id" to a user connecting remotely to a service we have here.
When that gap appeared we went into security freak out mode - thinking some hacker attempted 10,000 hits against this service. The only way I could duplicate the gap was to do a bad INSERT - with the username null, for example - which further drove up the hacker paranoia!
It was nice to find a real explanation!
-
TheSQLGuru (11/14/2013)
Nevyn (11/14/2013)
To DBAs, this isn't important, because *we* know that identity columns are arbitrarily assigned. However, the users of the report in question are accustomed to seeing mostly consecutive values in the report column that displays the identify values.
This sounds like a job for row_number()
That's an AWFULLY expensive operation to fix what is essentially a USER TRAINING issue (since the OP can't change the application)!!!
I meant to display on the report, not to generate identities 🙂
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply