Sql-server – Lock Escalation happening while inserting few million records into a table in production

blockinginsertisolation-levellockingsql server

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.

Best Answer

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
Related Question