Skip to main content

You could enabled RCSI (Read Committed Snapshot Isolation) which should allow users to access a snapshot of the tables that are being updated. (This increases the usage of the tempdb so make sure it can handle the extra load)

http://www.brentozar.com/archive/2013/01/implementing-snapshot-or-read-committed-snapshot-isolation-in-sql-server-a-guide/

http://www.brentozar.com/archive/2015/03/monitoring-snapshot-isolation-with-perfmon-in-sql-server-video/

I would look into the import process as RLF mentioned. If the inserts and updates are being done from a single connection and that connection wraps everything it does in a transaction that is not committed until all of the inserts and updates are done that would cause the blocking. I would also look at where the data is coming from to perform the inserts and updates. If the data is coming from a select that is nested in the transaction that might explain the locking on a table that is not involved in an insert or update action.

This increases the usage of the tempdb so make sure it can handle the extra load:

You could enabled RCSI (Read Committed Snapshot Isolation) which should allow users to access a snapshot of the tables that are being updated. (This increases the usage of the tempdb so make sure it can handle the extra load)

http://www.brentozar.com/archive/2013/01/implementing-snapshot-or-read-committed-snapshot-isolation-in-sql-server-a-guide/

http://www.brentozar.com/archive/2015/03/monitoring-snapshot-isolation-with-perfmon-in-sql-server-video/

I would look into the import process as RLF mentioned. If the inserts and updates are being done from a single connection and that connection wraps everything it does in a transaction that is not committed until all of the inserts and updates are done that would cause the blocking. I would also look at where the data is coming from to perform the inserts and updates. If the data is coming from a select that is nested in the transaction that might explain the locking on a table that is not involved in an insert or update action.

You could enabled RCSI (Read Committed Snapshot Isolation) which should allow users to access a snapshot of the tables that are being updated.

I would look into the import process as RLF mentioned. If the inserts and updates are being done from a single connection and that connection wraps everything it does in a transaction that is not committed until all of the inserts and updates are done that would cause the blocking. I would also look at where the data is coming from to perform the inserts and updates. If the data is coming from a select that is nested in the transaction that might explain the locking on a table that is not involved in an insert or update action.

This increases the usage of the tempdb so make sure it can handle the extra load:

Source Link
Aaron
  • 1.8k
  • 1
  • 11
  • 23

You could enabled RCSI (Read Committed Snapshot Isolation) which should allow users to access a snapshot of the tables that are being updated. (This increases the usage of the tempdb so make sure it can handle the extra load)

http://www.brentozar.com/archive/2013/01/implementing-snapshot-or-read-committed-snapshot-isolation-in-sql-server-a-guide/

http://www.brentozar.com/archive/2015/03/monitoring-snapshot-isolation-with-perfmon-in-sql-server-video/

I would look into the import process as RLF mentioned. If the inserts and updates are being done from a single connection and that connection wraps everything it does in a transaction that is not committed until all of the inserts and updates are done that would cause the blocking. I would also look at where the data is coming from to perform the inserts and updates. If the data is coming from a select that is nested in the transaction that might explain the locking on a table that is not involved in an insert or update action.