Once you’ve analyzed the cause of a block, the next step is to determine any possible resolutions. Here are a few techniques you can use to do this:
|Use a covering index on the contended data
||If the query of one of the processes can be satisfied using a covering index, then it will prevent the process from requesting locks on the contended resource
|Optimize the queries executed by blocking and blocked SPIDs
||E.g. break UPDATE/DELETE statements into multiple batches using the appropriate WHERE clauses. If the individual statements of the batch are executed in separate transactions, then fewer locks will be held on the resource within one transaction, and for shorter time periods.
|Decrease the isolation level
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED or
WITH (NOLOCK) locking hint.
|Partition the contended data
||This way, exclusive locks will be acquired at partition level, so it will increase concurrency.