0

I want to insert few million records into a table in my production database. I am not updating any existing rows.I am just doing

INSERT INTO TAB1 SELECT COL1,COL2,COL3 FROM TAB2

This table has an Identity column as primary key and a non clustered key.

When i do this i get blocking and then it gets escalated to chain of bolocking. I know that by default SQL Server doesn't do TABLE lock and default isolation level is READ COMMITTED.

Since i am inserting new rows into the table,i am sure that the application is not acquiring any shared lock(select while insert scenario).

I am confused as to why i am getting this blocking and then chain of block.

I am thinking of giving an hint to perform ROW LEVEL lock only or use any other lock hints.

Any suggestion.

Additional Info:

The target table into which i am inserting rows is being replicated using transactional replication.I am not sure whether it is affecting the insert process.

  • Here are some examples michaeljswart.com/2014/09/take-care-when-scripting-batches ( it's for delete ,but can be used/converted also for insert) or this mssqltips.com/sqlservertip/5636/… – Sabin Bio Jul 16 at 5:14
  • Look at your fill factor value. If you have it set in the mid to upper 90s you maybe waiting on disk io to allocate more space to the table since you are using an identity column for the primary key. – Aaron Jul 16 at 6:08
  • To avoid the lock escalation you could insert your data in short batches or disable the lock escalation for the table (SET (LOCK_ESCALATION = DISABLE)) of for the session (trace flag 1224) – Denis Rubashkin Jul 16 at 7:25
  • Please look at query wait type. It'll give you more idea about reason behind lock escalation. – Rajesh Ranjan Jul 16 at 7:39
1

You can check for any Foreign Key,constraint,trigger etc define on table and give detail of it.

You can use TabLock hint].

Also if there is no chance of Getting READ UNCOMMITTED data then you can use NoLock hint in Select .

INSERT INTO TAB1 WITH (TABLOCK)
SELECT 
COL1,COL2,COL3
FROM TAB2

Identity column as primary key is not an issue. Non cluster Index can be and issue.

So as second option,you can disable index then rebuild later.It is also time consuming for million of records.

ALTER INDEX IX_IndexName ON TAB1 DISABLE;
GO

INSERT INTO TAB1 WITH (TABLOCK)
    SELECT 
    COL1,COL2,COL3
    FROM TAB2

ALTER INDEX IX_IndexName ON Tab1 REBUILD;
GO

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.