CODEWITHSUNDEEP
sql-servert-sql
BrianK
BrianK

Reputation: 2705

Multiple Insert Statement blocking small one

Say I have one insert statement going where the values come from a select statement from another table. Therefore, many records are inserted at once. I have another process that just needs to insert a single record. How can I get SQL Server to let the single insert statement execute in a more timely manner? In my observations, the single one gets blocked for quite some time while the multiple insert runs. It would good if the single could "slip in". I tried adding WITH (ROWLOCK) on the inserts.

Upvotes: 1

Views: 651

Answers (1)

StuartLC
StuartLC

Reputation: 107317

It is possible that the bulk insertion is escalating to table locks. You can potentially reduce table locks by changing the table DDL to LOCK_ESCALATION=DISABLE, although this could degrade the bulk insert performance.

An alternative is to rewrite the bulk INSERT / SELECT INTO to insert in batches such that it never holds more than 5000 locks at a time.. This would however change the scope of each unit of work, as you will now commit after each smaller batch, which may not be desirable.

Upvotes: 1

Related Questions