SET IDENTITY_INSERT ON + locks

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

SET IDENTITY_INSERT ON + locks

Author

Topic

shiloh
Starting Member

48 Posts

Posted - 2007-06-21 : 14:45:16

Hello all

We have a table with over 6-700 mill rows. We have a job that periodically archives some data based on some business logic. Users can de-archive their data whenever they want. Currently, due to some business constraints when we dearchive we need to maintain the ID for each row. So in our procs we have


SET IDENTITY_INSERT ON
INSERT INTO Table
SELECT ... FROM ArchiveDB.dbo.Table
WHERE...
SET IDENTITY_INSERT OFF

We do see heavy blocking on these dearchiving procs. One argument that's floating around the DBA's here is, the SET IDENTITY_INSERT ON will do a table lock blocking out other inserts. and because its such a huge table its causing contention, blocking etc. Some argue SQL Server doesnt do table lock, just page level lock and depending on the number of rows being inserted ) If > couple of thousand) it might escalate to table lock.
Anyone has any idea which part of above statements are true, if any?

thanks,
Don

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-06-21 : 14:53:56

Do the inserts in batches. We do them in 10,000 row batches and loop until there are no more rows to process. You might want to do an even smaller batch. Make sure to turn the identity_insert option on and off in each batch.

This method allows other processes to access the table in between each batch, so you will still encounter blocking but it'll be for shorter periods of time.

SQL Server typically starts at row-level locking and escalates as needed.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

shiloh
Starting Member

48 Posts

Posted - 2007-06-21 : 16:32:27

Yes Tara, the INSERT is in batch. but we do have the SET IDENTITY_INSERT ON and set it to OFF at the end of the proc. the question is does it do a table lock and hold off other inserts while this completes or does it just do a page lock and escalate to table lock when necessary?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-06-21 : 16:40:30

BOL doesn't provide that information, so I don't know. Check out sp_lock to see what is going on.

Tara Kizer
http://weblogs.sqlteam.com/tarad/

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2007-06-22 : 10:11:58

SET IDENTITY ON does not block other inserts. It's scope is the connection, not the database or even the table.

e4 d5 xd5 Nf6

shiloh
Starting Member

48 Posts

Posted - 2007-06-22 : 12:18:27

I am not sure if its blockingo ther inserts but it is locking the table. Trace files investigation always comes back to these procs. and all they do is set the IDENTITY_INSERT on, do an insert (usually about 20-50 rows) and set it to off. But the procs do get called often. A nightly job archives the data and users can dearchive them during the day. So during peak loads we do see blocking. we chase the spids and its comes down to these procs.

I am working on a logic/design change so as not to retain the ID numbers but thats a big change. I just wanted to know about the internal behaviour of this SET IDENTITY_INSERT ON. didnt find anything in BOL.

Kristen
Test

22859 Posts

Posted - 2007-06-25 : 08:15:01

Reduce the loop size to, maybe, one insert and see if that cures it?

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources