February 26, 2014 at 5:02 am
Hi guys,
Has anyone come across a DBCC CHECKIDENT RESEED being very slow? We've reached the INTs 2.n billion limit on the identity column but have a 2.3 million gap in the table (and many more gaps).
The third party vendor won't support a change to BIGINT & don't know the reason for the big gaps.
In the short term I'm looking to fill the gaps using the RESEED (obviously the issues are much bigger but its a short fix). However, Google said this is supposed to be a quick change. I tried it but it was running for 12 minutes & still didn't finish. It's now been running for 8.5 minutes (I cancelled before).
Any idea why it might be so slow or how to avoid it? The table has 20.5 million rows, the current identity max value is 2.n billion (the identity max).
Edit: It seems it requires a table lock & select queries block it?
February 26, 2014 at 7:54 pm
All right. I ran a test using an XEvent session to get the locks acquired by DBCC CHECK_IDENT with a RESEED. The important locks acquired are a SCH-M (Schema Modification) on the target table, an IX (intent Exclusive) on syscolpars, and an X (Exclusive) lock on a key in syscolpars. According to the lock compatability matrix found in BOL here, the SCH-M lock is incompatible with S(hared) locks so a read should block the reseed.
It was pretty easy to duplicate the issue just by having a long running query take a shared lock on the table.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 26, 2014 at 8:03 pm
Sorry, I missed the "how to avoid it part" in my first post confirming the behavior. I actually had a hard time duplicating the behavior until I used a long running select against the table. I originally had 5 PoSH sessions running a Select MAX(I), COUNT(*) from table in a loop to try to generate some activity on the table, but that query was quick enough that even with the 5 sessions looping the RESEED could sneak in, get the SCH-M lock and make the change in between queries. When I put a single long-running query (Select * From table) that held a S lock long enough to keep the RESEED from getting the SCH-M lock. How to avoid it is hard, but the first step would be to try to find a time with the least activity to run it in, even if you had to run it before you filled in one of the gaps you have. The other thing is to try to identify long-running selects and try to tune them down so you can get in and get the SCH-M lock to run the RESEED operation.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 27, 2014 at 2:59 am
Oh I see, thanks for the info. I will have to read up on XEvent as I haven't heard of it before.
Is there any kind of known bugs with the identity column in 2008r2? Yesterday we reached the 2.1 billion count limit (4 byte int) despite only having 20 million rows. Some gaps between identity values are over 1 million.
The only thing I've managed to find is a CACHE/NOCACHE option but it seems that wouldn't use such a large cache amount (and seem it only causes gaps on system crashes + MSSQL 11).
Yesterday I RESEEDED to the beginning of a 2.3 million range gap. Over the course of the day only 5,968 rows were inserted within that 2.3 million row range (it eventually exceeded the range -> duplicate issues). The server hasn't crashed or anything.
My assumption is an application bug but I was just checking that there isn't a 2008r2 bug related to sequences?
I found this forum post: http://social.msdn.microsoft.com/Forums/en-US/fd821aa1-f39a-4cfb-9843-effdadb3bd6f/identity-column-jumpednot-sequential-in-sql-2008-r2?forum=transactsql
Is this correct? I was under the assumption that an identity column was the same as an Oracle sequence i.e. incrementing number rather than "unique, not sequential". If not sequential I don't see why they would bother providing seed & increment options. I also don't see why SQL Server would "optimize" by jumping 40,000 numbers when less than 6000 rows were inserted over a day.
Edit: It is running SP1, the only "identi" related search of the SP2 bug fixes is "820859 - Fixes an issue in which an incorrect value is returned when you run SCOPE_IDENTITY(). "
February 27, 2014 at 4:53 am
Dird,
I am not aware of any bugs with the generation of identity values. There are 3 ways you can get gaps in identity values:
1. Failed inserts. This causes gaps because of the way identity values are generated. Basically on an insert SQL Server goes out and grabs the next identity value and increments it and then does the insert. If the insert fails and the transaction has to roll back the identity value is not rolled back. Here's a simple script that shows the behavior:
IF OBJECT_ID('dbo.test', 'U') IS NOT NULL
BEGIN
DROP TABLE test;
END
GO
CREATE TABLE test
(
id INT IDENTITY(1, 1) ,
colA TINYINT
);
GO
/* Identity Value is NULL */
DBCC CHECKIDENT('dbo.test', NORESEED);
GO
/* INsert fail because 5000 is bigger than tinyint*/
INSERT INTO test
( cola )
VALUES ( 5000 );
GO
/* Idneityt value is now 1 */
DBCC CHECKIDENT('dbo.test', NORESEED);
GO
/* insert succeds */
INSERT INTO test
( cola )
VALUES ( 5 );
GO
/* Identity Value is now 2 */
DBCC CHECKIDENT('dbo.test', NORESEED);
GO
2. Deletes
3. Manually create the identity value using SET IDENTITY INSERT
ON;. Here's a simple example of that:
IF OBJECT_ID('dbo.test', 'U') IS NOT NULL
BEGIN
DROP TABLE test;
END
GO
CREATE TABLE test
(
id INT IDENTITY(1, 1) ,
colA TINYINT
);
GO
/* IDentity Value is NULL */
DBCC CHECKIDENT('dbo.test', NORESEED);
GO
/* INsert a row*/
INSERT INTO test
( cola )
VALUES ( 5 );
GO
/* Idneityt value is now 1 */
DBCC CHECKIDENT('dbo.test', NORESEED);
GO
/* insert a row setting the id value explicitly */
SET IDENTITY_INSERT dbo.test ON;
INSERT INTO test
( id, cola )
VALUES ( 5000000, 6 );
SET IDENTITY_INSERT dbo.test ON;
GO
/* Identity Value is now 5,000,000 */
DBCC CHECKIDENT('dbo.test', NORESEED);
GO
My guess is that the app has either 1 or 2 going on, creating the gaps.
Identity is not exactly like Oracle's Sequence and in 2012 SQL Server added a SEQUENCE. THis gets quite a few people because no one expects gaps.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 27, 2014 at 2:36 pm
Could have sworn I already replied to this :f
The reason I have a hard time believing 1/2 are likely is because of the gap ranges. 7-8 times the sequence gaps have been over 1 million. There shouldn't be any kind of inserts on that level (6000 yesterday).
That said I looped 10,000 with the same kind of table structure (cluster index on some random column, non-clustered index on the identity PK) but never had a gap. Tried updates in the hopes it might have done something but nope. I guess I'll try a loop tomorrow starting with numbers around the ranges where the skips happened; just to rule out some weird dislike of the numbers by sql server).
It worked fine from 2006-2012 then suddenly started having issues in Jan 2013 so I'm hoping some application/code update was carried out around that time.
As far as I'm aware the only difference between a sequence & identity column is the scope (identity = single column sequence)?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply