"No one is harder on a talented person than the person themselves" - Linda Wilkinson ; "Trust your guts and don't follow the herd" ; "Validate direction not destination" ;

January 22, 2010

Troubleshooting blocking

Blocking Definiton - Attempting to acquire a lock that conflicts with a lock held by another connection.
Deadlock Definition - A deadlock is a circular blocking chain, where two or more threads are each blocked by the other so that no one can proceed (Source)

Demo Blocking
Create simple tables for test purpose
--STEP 1

CREATE TABLE TESTTable
(Id Int Identity(1,1),
Name VARCHAR(50))

--STEP 2
--Populate Data
DECLARE @i int
SET @i = 0
WHILE 1 = 1
BEGIN
INSERT INTO TESTTable(Name)
SELECT CONVERT(VARCHAR(20),@i)+'Test'
SET @i = @i+1
IF @i > 10000
BREAK;
END

--STEP 3
BEGIN TRAN
Update TESTTable
SET Name = '1Test1'
WHERE Name = '1Test'

Open another new session (Not a gr8 query a little example to block it, Transaction is open in both the cases)
STEP 1
BEGIN TRAN
Update TESTTable
SET Name = '1Test1'
WHERE Name = '1Test'

Open another session to detect blocking

STEP 1
Run the below query to check for blocking. This would show current blocking. More details at statement level run query provided in next step.
SELECT * FROM sys.sysprocesses WHERE blocked <> 0
STEP 2
One more blocked query script. This would show blocking and waiting statement and details.
Source - http://www.microsoft.com/technet/scriptcenter/scripts/sql/sql2005/trans/sql05vb044.mspx?mfr=true
 
This query would show
STEP 3

Setup blocked process report as provided in link
Monitoring for Blocked Processes On SQL 2005 - Extended Version
This is very clearly explained in provided link. 
When To Use Blocked Processes Reports
Zeroing in on blocking on seemingly unrelated tables
CodePlex - SQL Server Blocked Process Monitor
Troubleshooting Blocking 101 on SQL Server


Happy learning...

No comments: