DMV Queries - Get Blocking/Blocked Processes

Applicability:

                 SQL Server 2000:        Not Supported
                 SQL Server 2005:        Tested
                 SQL Server 2008:        Tested
                 SQL Server 2008R2:    Tested
                 SQL Server 2012:        Not Tested        

Credits:

Author:    Unknown
Date:        2 Aug 2012

Description

Provides a summary of blocked processes and the processes that are blocking them.
If a Database Name is supplied then the scope will be restricted to the database, otherwise all blocked processes on the server will be returned. 

Code

Function:


DROP FUNCTION dbo.udf_GetBlockingProcesses
GO

CREATE FUNCTION dbo.udf_GetBlockingProcesses (@DatabaseName SYSNAME)
RETURNS @BlockingProcesses TABLE
  
(
      
DatabaseName            SYSNAME NULL,
      
BlockedSPID             INT,
      
BlockedQuery            VARCHAR(MAX),
      
BlockedResource         VARCHAR(100),
      
BlockingSPID            INT,
      
BlockingSQL             VARCHAR(MAX),
      
DATETIME                DATETIME
  
)
AS
/***************************************************************
Purpose:   To retrieve details of all currently blocked
           processes and the processes that are blocking them.
           Also includes the TSQL being run by the both processes
          
           The following DMVs are used:
               sys.dm_exec_connections - contains a record of all connections on the server
               sys.dm_exec_requests    - contains a record of all active requests on the server
               sys.dm_exec_sql_text    - contains the sql text being run by each request          
          
Author:        Unknown
History:   19 Aug 2009 - Initial Issue
           1 Aug 2012 - ChillyDBA - converted to TVF and extended to return the parent queries too.

****************************************************************/

BEGIN

   INSERT
@BlockingProcesses
      
(
          
DatabaseName,
          
BlockedSPID,
          
BlockedQuery,
          
BlockedResource,
          
BlockingSPID,
          
BlockingSQL,
          
DATETIME
      
)
      
      
-- get  the blocked processes and the process that is blocking

  
SELECT
      
DB_NAME(Blocked.database_id)                    AS DatabaseName,
      
Blocked.Session_ID                              AS BlockedSPID,
      
Blocked_SQL.TEXT                                AS BlockedQuery,
      
Waits.wait_type                                 AS BlockedResource,
      
Blocking.Session_ID                             AS BlockingSPID,
      
Blocking_SQL.TEXT                               AS BlockingSQL,
      
GETDATE()                                       AS DATETIME
   FROM
sys.dm_exec_connections AS Blocking
  
INNER JOIN sys.dm_exec_requests AS Blocked
      
ON Blocked.Blocking_Session_ID = Blocking.Session_ID
  
INNER JOIN sys.dm_os_waiting_tasks AS Waits
      
ON waits.Session_ID = Blocked.Session_ID
  
CROSS APPLY sys.dm_exec_sql_text(Blocking.most_recent_sql_handle) AS Blocking_SQL
  
CROSS APPLY sys.dm_exec_sql_text(Blocked.sql_handle) AS Blocked_SQL
  
WHERE Blocked.database_id = ISNULL(DB_ID(@DatabaseName),Blocked.database_id)
  
  
RETURN
END


Test Code:


SELECT * FROM dbo.udf_GetBlockingProcesses (NULL)
SELECT * FROM dbo.udf_GetBlockingProcesses ('master')


ċ
udf_GetBlockingProcesses.sql
(2k)
Andy Hughes,
Aug 2, 2012, 6:48 AM
Comments