12 November,2012 by Tom Collins
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
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?
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.
How to find Open SQL Transactions
How to improve SQL Execution Plan Reuse
How to troubleshoot a slow running SQL query
This is only a preview. Your comment has not yet been posted.
As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.
Having trouble reading this image? View an alternate.
Posted by: |