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 (
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,
INSERT INTO #blocking
WHERE SPID IN (SELECT BlkBy FROM #blocking WHERE LTRIM(RTRIM(BlkBy)) NOT IN ('.'))
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.