Who is blocking my query?

Do you have a query that suddenly takes forever to finish or doesn’t finish at all?
Luckily, SQL Server has ready made tools to solve this kind of problems.
There is a SP called ‘sp_who2’ that compiles data from a system table called ‘sys.sysprocesses’ in the master database.
You can look at the code of ‘sp_who2’:

To find this kind of blocking problems I have compiled the data even further.
Not only does it tell you what process is being blocked but also the SPID number of the blocker.
It also gives you the SQL code involved.

————————————

–Block finding query
—
DECLARE @Table TABLE
(
SPID INT ,
Status VARCHAR(MAX) ,
LOGIN VARCHAR(MAX) ,
HostName VARCHAR(MAX) ,
BlockedBy VARCHAR(MAX) ,
DBName VARCHAR(MAX) ,
Command VARCHAR(MAX) ,
CPUTime INT ,
DiskIO VARCHAR(MAX),–INT ,
LastBatch VARCHAR(MAX) ,
ProgramName VARCHAR(MAX) ,
SPID_1 INT ,
REQUESTID INT
)
INSERT INTO @Table
EXEC sp_who2
SELECT ‘IsBlocked’ AS Descr ,
t.spid,
t.status,
t.login,
t.hostname,
t.DBName,
t.Command,
t.LastBatch,
qt.text as SqlCommand
FROM @Table t
JOIN sys.sysprocesses ss ON ss.spid=t.SPID
OUTER APPLY sys.dm_exec_sql_text(ss.sql_handle) AS qt
WHERE LTRIM(RTRIM(t.BlockedBy)) <> ‘.’
UNION
SELECT ‘Blocking’ AS Descr ,
t.spid,
t.status,
t.login,
t.hostname,
t.DBName,
t.Command,
t.LastBatch,
qt.text
FROM @Table t
JOIN sys.sysprocesses ss ON ss.spid=t.SPID
OUTER APPLY sys.dm_exec_sql_text(ss.sql_handle) AS qt
WHERE t.SPID IN ( SELECT t.BlockedBy
FROM @Table t
WHERE LTRIM(RTRIM(t.BlockedBy)) <> ‘.’
)
———————————————

If you run the query above and there is no output, no processes are blocked.
Great 🙂

If you want to see what it looks like:
1) Run the first query below in one window in the SQL Management Studio
2) Run the second query below in another window
3) Run the block finding query above in a third window

————————————
–Create a blocking process (1/2)
–Drop test table if it exists
IF OBJECT_Id(‘tempdb..##test’, ‘U’) IS NOT NULL
DROP TABLE tempdb..##test

CREATE TABLE ##test(
[Id] [int] PRIMARY KEY CLUSTERED NOT NULL,
[Name] [varchar](20) NOT NULL
);

insert into ##test(Id,Name) values(1,’A name’)
GO

BEGIN TRANSACTION
UPDATE ##test
SET Name = ‘Another Name’
WHERE Id = 1
———————————–
–Create a blocking process (2/2)
UPDATE ##test
SET Name = ‘A Third Name’
WHERE Id = 1
————————————–

The output from the block finding query should look something like this: