12 November,2012 by Jack Vamvas
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
1) There are a number of tools to identify blocking. Some examples are :
a) sp_blocker_pss80 script
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?
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.