How to detect and troubleshoot blocking and deadlocks

There are two main scenarios in blocking. First, one task uses a resource, blocking another task from using the same resource - called Blocking. Secondly, two or more tasks block each other permanently by possessing a resource lock required by the other task - also known as a Deadlock

Blocking

1) There are a number of tools to identify blocking. Some examples are :

a) sp_blocker_pss80 script

b) SQL Server – Current queries executing on SQL Server

c) sp_who2

d) SQL Server – Perfstats guide - SQL Server DBA

e) Blocked processes - SQL Server DBA

f) Blocked Porcess Report -SQL Server – Find queries causing Lock Timeouts per second

2) If you detect blocking , check is for Open transactions.

Read SQL Server - SQL open transactions and how to find  for details on using DBCC OPENTRAN()   and DBCC INPUTBUFFER() for details.

If you prefer , use the DMV sys.dm_tran_database_transactions and sys.dm_tran_session_transactions  , which returns similar information.Read this post for script using the DMVs - SQL Server - How to find Open Transactions

3) Focus on the queries. Analyse the Transaction handling . Are the COMMIT and ROLLBACK  applied efficiently?

Deadlock

 The other scenario creating blocking is in the Deadlock Scenario. The Deadlock occurs because at least two tasks block each other permanently.

 1) Identify the Deadlocks. Various methods exist to locate and report on Deadlock details.

Profiler - Profiler Deadlock Graph Event

Trace Flags - 1204 and 1222

2) If Deadlocks are occurring – review the details of the deadlock. Analyse the SPIDs and the commands issued

3) Drill into the Transaction Logic.

a)  Consider the order of the transactions within the stored procedure.
b) If it’s a long running transaction , the potential scenario is  multiple transactions are running via multiple sessions.A few millisecond transactions would create a situation where deadlocks can occur , if resources are not released efficiently
c) Are so many steps required ?
d) Can BEGIN TRANS ...COMMIT TRANS  be placed around more sub steps , to release locks?
e)A way must be found to release the (probably ) exclusive locks created by the UPDATE,DELETES,INSERTS, without upsetting the data integrity. Speak to the developers , and consider the implications of adjusting transaction management.

 Read More

How to find Open SQL Transactions

How to improve SQL Execution Plan Reuse

How to troubleshoot a slow running SQL query