January 22, 2008 at 7:39 am
The process:
START LOOP:
- We import a data file into a heap worktable using BCP (up to several thousand records at a time).
- Worktable has an IDENTITY column to uniquely number each record.
- Data in the table is processed, then copied to a permanent table (with an INT id column to hold the Identity value generated in the worktable)
- The current maximum ID value in the worktable is copied to a variable
- The worktable is truncated (for speed and less logging), which of course re-sets the identity counter to zero
- DBCC Checkident (worktablename, RESEED, (@old_ident_value + 1) ) to prepare the worktable to accept the next file import
- We import a data file into a worktable using BCP (up to several thousand records at a time).
END LOOOP:
Recently, the time taken to complete the RESEED has taken hugely excessive lengths of time to complete (the worst case was 715 seconds), although sometimes it could be as fast as 49 milliseconds.
Using Profiler, and recording the Duration, CPU, Reads and Writes for each RESEED in turn, showed the following:
Increased Duration was cyclical - plotting on a chart showed repeated peaks and troughs, each peak on a roughly 5-minute cycle. Irrespective of the Duration, the Reads and Writes were always identical. CPU showed variation consistent with the increased duration (i.e. Increased duration went hand-in-hand with increased CPU).
The only thing I can find that would operate on a roughly 5-minute cycle would be the checkpoint process (given the default recovery interval), but since the RESEED is working on an empty table every time I can't really see why this should vary at all.
Is there something about the workings of DBCC CHECKIDENT that I'm not aware of?
Anyone else ever seen similar behaviour?
Phil
January 22, 2008 at 9:17 am
Very interesting and I wish I had an answer. This almost seems like the RESEED is scanning the huge table which has been marked for truncated, but not truncated.
Sounds like a bug.
January 22, 2008 at 6:39 pm
I think there's some unintuitive behavior in the server here around worktables. Let me check with some people in the dev team and get back to you.
Thanks
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
January 22, 2008 at 7:16 pm
Just curious - why not drop the table and create the table all over again? with the new seed?
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 24, 2008 at 2:41 am
No reason why the table couldn't be dropped, clearly. In my defence, the code was written long before my time (that old excuse again ! 🙂 ) . It would be a little fiddly getting the re-seed value into the CREATE TABLE command - would have to be done as dynamic SQL. Nothing wrong with that, but the RESEED command seemed much more elegant.
We've modified the code now so that it truncates the table, sets identity insert ON, inserts a dummy row with the required IDENTITY seed value specified explicitly, deletes that row, sets identity insert OFF, and then bulk inserts the file again.
A bit more cumbersome, but it works - and in fractions of a second too.
So, now I'm just intrigued with the original DBCC RESEED question from a purely academic point of view. All additional thoughts and suggestions gratefully received ...
January 24, 2008 at 2:43 am
Paul,
Regarding the worktable, it isn't a #table worktable, just a simple, heap local table in the database.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply