How to: Identify Blocked SQL Processes Quickly

There was a great article in Visual Studio magazine (June 2008) by Ian Stirk in which he talks in detail about how to improve application performance by creating a utility that tells you which processes are being blocked.

You can read the article at https://visualstudiomagazine.com/features/article.aspx?editorialsid=2490. The two sql sprocs that you will need to create are:

The database utility dba_BlockTracer extracts data about the running processes by inspecting the system view sys.sysprocesses. This view then queries the underlying system table sysprocesses.

CREATE PROC [dbo].[dba_BlockTracer] AS /*--------------------------------------------------   Purpose: Shows details of the root blocking process, together with details of any blocked processed ---------------------------------------------------- Parameters: None. Revision History:       19/07/2007 Ian_Stirk@yahoo.com Initial version Example Usage:    1. exec YourServerName.master.dbo.dba_BlockTracer --------------------------------------------------*/ BEGIN    -- Do not lock anything, and do not get held up by any locks.    SET TRANSACTION ISOLATION LEVEL READ       UNCOMMITTED    -- If there are blocked processes...    IF EXISTS(SELECT 1 FROM sys.sysprocesses WHERE       blocked != 0)    BEGIN       -- Identify the root-blocking spid(s)       SELECT distinct t1.spid AS [Root blocking spids]          , t1.[loginame] AS [Owner]          , master.dbo.dba_GetSQLForSpid(t1.spid) AS             'SQL Text'          , t1.[cpu]          , t1.[physical_io]          , DatabaseName = DB_NAME(t1.[dbid])          , t1.[program_name]          , t1.[hostname]          , t1.[status]          , t1.[cmd]          , t1.[blocked]          , t1.[ecid]       FROM sys.sysprocesses t1, sys.sysprocesses t2       WHERE t1.spid = t2.blocked         AND t1.ecid = t2.ecid         AND t1.blocked = 0       ORDER BY t1.spid, t1.ecid       -- Identify the spids being blocked.       SELECT t2.spid AS 'Blocked spid'          , t2.blocked AS 'Blocked By'          , t2.[loginame] AS [Owner]          , master.dbo.dba_GetSQLForSpid(t2.spid) AS             'SQL Text'          , t2.[cpu]          , t2.[physical_io]          , DatabaseName = DB_NAME(t2.[dbid])          , t2.[program_name]          , t2.[hostname]          , t2.[status]          , t2.[cmd]          , t2.ecid       FROM sys.sysprocesses t1, sys.sysprocesses t2       WHERE t1.spid = t2.blocked         AND t1.ecid = t2.ecid       ORDER BY t2.blocked, t2.spid, t2.ecid    END    ELSE -- No blocked processes.       PRINT 'No processes blocked.' END

Make a call to the database function dba_GetSQLForSpid to get the underlying SQL, which can show you why performance is slow. The function accepts one parameter: the SQL Server process ID (@spid) of a running process.

CREATE Function [dbo].[dba_GetSQLForSpid] (    @spid SMALLINT ) RETURNS NVARCHAR(4000) /*------------------------------------------------- Purpose: Returns the SQL text for a given spid. --------------------------------------------------- Parameters: @spid - SQL Server process ID. Returns: @SqlText - SQL text for a given spid. Revision History:       01/12/2006 Ian_Stirk@yahoo.com Initial version Example Usage:    SELECT dbo.dba_GetSQLForSpid(51)    SELECT dbo.dba_GetSQLForSpid(spid) AS [SQL text]       , * FROM sys.sysprocesses WITH (NOLOCK) --------------------------------------------------*/ BEGIN    DECLARE @SqlHandle BINARY(20)    DECLARE @SqlText NVARCHAR(4000)    -- Get sql_handle for the given spid.    SELECT @SqlHandle = sql_handle       FROM sys.sysprocesses WITH (nolock) WHERE       spid = @spid    -- Get the SQL text for the given sql_handle.    SELECT @SqlText = [text] FROM       sys.dm_exec_sql_text(@SqlHandle)    RETURN @SqlText END

Comments

• Anonymous
  July 13, 2008
  PingBack from http://wordnew.acne-reveiw.info/?p=8370

• Anonymous
  April 07, 2010
  Incredibly helpful - have been trying for hours to resolve a deadlock issue that came out of nowhere, and dba_GetSQLForSpid was just the ticket. Thanks.