A closer look at everything related to SQL Server

Archive for June, 2014

SQL 2012 Identity column Unwanted Feature

(Applies to SQL Server 2012 Enterprise Edition only)

Problem:
A feature or bug in SQL Server 2012 results in an unexpected behavior for identity columns. Technically, the specification for SQL does not dictate that identity columns be numerically contiguous, only that they are steadily increasing. In previous versions of SQL Server identity columns had been implemented by Microsoft to be contiguous, but in 2012, Microsoft changed the behavior so that identity columns cache values differently and may no longer be contiguous.

In practice what tends to happen is that when SQL Server service is restarted, failed over, hits a log checkpoint or possibly under other circumstances, you may see a number jump in identity column numbers, for example:

1, 2, 3, 4, (restart occurs), 1001, 1002, 1003, 1004, 1005, (failover occurs), 2000, 2001, 2002. . .

If an application is expecting contiguous numbering, this may result in unexpected issues. This can cause serious data integrity issue.

Solution:
There is a trace flag 272 that can be enabled to force the contiguous numbering behavior from previous versions of SQL Server. The downside is slightly slower identity column values upon insert, which should merely result in the same identity column performance from previous versions. You can read the comments SQL Server community leaders has sent to Microsoft on this issue.
http://connect.microsoft.com/SQLServer/feedback/details/739013/failover-or-restart-results-in-reseed-of-identity

Test:
I reproduced the error using sql server 2012 on my laptop. Anybody can use it on test server to see the issue.

— Example is using AdventureWorks2012.Production.WorkOrder table
— Check if table has identity column using sp_help [production.workorder]

— First Check the latest value of identity column
select max(workorderID) from production.workorder — 72591

— Now insert a row
INSERT INTO [Production].[WorkOrder]
([ProductID]
,[OrderQty]
,[ScrappedQty]
,[StartDate]
,[EndDate]
,[DueDate]
,[ScrapReasonID]
,[ModifiedDate])
VALUES
(1
,1
,1
,getdate()
,Sysdatetime()
,getdate() +1
,1
,Sysdatetime())
GO

— Check the value of identity column
select max(workorderID) from production.workorder — 72592

——–*************** Restart sql server service *************——————
— Insert another row
INSERT INTO [Production].[WorkOrder]
([ProductID]
,[OrderQty]
,[ScrappedQty]
,[StartDate]
,[EndDate]
,[DueDate]
,[ScrapReasonID]
,[ModifiedDate])
VALUES
(2
,2
,2
,getdate()
,Sysdatetime()
,getdate() +1
,2
,Sysdatetime())
GO

— Finally check the identity colum value again. This time it jumped to 73592, an increase of 1000.
select max(workorderID) from production.workorder — 73592

Result:

After enabling traceflag -T272, I repeated the previous steps and there was identity column increase of 1. I repeated this test on Developer edition SQL server 2012 and got increase of 2 after enabling traceflag. So at this point this traceflag only helps in Enterprise Edition only.

WorkOrderID ProductID OrderQty StockedQty ScrappedQty StartDate
72591 531 26 26 0 7/3/08 0:00
72592 1 1 0 1 6/16/14 14:36
73592 2 2 0 2 6/16/14 14:37
73593 3 3 0 3 6/16/14 14:37
74592 4 4 0 4 6/16/14 14:38
75593 4 5 0 5 6/16/14 14:42
75594 4 6 0 6 6/16/14 14:43
75595 4 7 0 7 6/16/14 14:44
75596 4 8 0 8 6/16/14 14:46

Advertisements

SQL Server 2012 Online indexing bug

There is a SQL 2012 bug found (http://sqlperformance.com/2014/06/sql-indexes/hotfix-sql-2012-rebuilds) when index rebuilt is done with ONLINE option.

A related KB article (http://support.microsoft.com/kb/2969896) states that:
The online index rebuild can cause index corruption or data loss when it is used together with concurrent queries that modify many rows. For example, a replication updates many rows on a table when the online index build is running on the same table concurrently.

This issue occurs when the following conditions are true:

  1. The online index build query runs in parallel mode.
  2. Deadlock error and a fatal error such as “lock timeout” occur in a specific order.

Note To work around this issue, run the online index build in serial mode (DOP = 1).

According to the article link I pasted above, SQL server 2012 SP2 is out now. But fix for this bug is not included because it was discovered recently. So it suggests that you DO NOT apply SP2 until CU for this bug is available.
In the meantime, we have these options.
1. Add maxdop = 1 in the index rebuild query with ONLINE
OR
2. Run defrag with ONLINE = OFF
OR
3. Apply hotfix (http://support.microsoft.com/kb/2969896) available for sql server 2012 SP1. As with any hotfix, they are not tested thoroughly and can introduce new bugs.
OR
4. Wait for SP2 CU2, targeted to release by end of July and apply both of them together to SQL server 2012 SP1.