A Modified Version Of sp_who2 To Identify Database Blocking
As a Database Administrator I’m constantly keeping an eye on the heath of our servers and individual databases. On occasion I’m called upon to identify why the system is responding slowly or why a report is taking too long to build. Generally my first step is to run the sp_who2
command in SQL Management Studio. This command displays information on the current user sessions and processes within a deployment of SQL Server. Typically I’m looking in the BlkBy column to see if any connections are being blocked by another connection. If so, and depending on what is doing the blocking, I will kill the assigned SPID to clear things up.
The only thing about just running sp_who2
is that you have to sift through all the data returned and look at all individual records to determine which processes are blocking. If you have a cascade of blocking this can become somewhat tedious and generally requires you to export the data to a spreadsheet in order to better sort the results and get a clear picture of whats going on.
However, with a few small modifications to the built in sp_who2
command, we can create an alternate procedure to only pull back the records that are being blocked. This greatly simplifies the process of identifying the culprit and allows you to resolve the issue much more quickly.
CREATE TABLE #blocking (
spid INT,
status VARCHAR(100) NULL,
login SYSNAME NULL,
hostname SYSNAME NULL,
blkby VARCHAR(10) NULL,
dbName SYSNAME NULL,
command VARCHAR(1000) NULL,
cputime INT NULL,
diskio INT NULL,
lastbatch VARCHAR(50) NULL,
programname VARCHAR(100) NULL,
spid_b INT,
requestid INT)
INSERT INTO #blocking
EXEC sp_who2
SELECT spid,
status,
login,
blkby,
dbname,
command,
cputime
FROM #blocking
WHERE SPID IN (SELECT BlkBy FROM #blocking WHERE LTRIM(RTRIM(BlkBy)) NOT IN ('.'))
UNION ALL
SELECT spid,
status,
login,
blkby,
dbname,
command,
cputime
FROM #blocking
WHERE LTRIM(RTRIM(BlkBy)) NOT IN ('.')
ORDER BY cputime
DROP TABLE #blocking
The best approach is to create a stored procedure using the above script so you can run it on the fly whenever blocking is occurring.
Its also possible to further modify the script to automatically kill any spids that are causing blocking beyond a certain threshold, by using the CPUTime data to determine how long the blocking has been occurring. However, it’s best to review the blocking before killing off connections as there are times where it is to be expected.