DMV Queries - Get Blocking Locks

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/ChillyDBA
Date:        7 May 2012/1 July 2012

Description

Provides a summary of blocking lock information.
If a Database Name is supplied then the scope will be restricted to the database, otherwise all locks on the server will be returned. 

 

Code

Function:


DROP FUNCTION dbo.udf_GetBlockingLocks
GO

CREATE FUNCTION dbo.udf_GetBlockingLocks (@DatabaseName SYSNAME)
RETURNS @BlockingLocks TABLE
  
(
      
RequestSessionID            BIGINT,
      
DatabaseName                SYSNAME NULL,
      
ResourceType                VARCHAR(100) NULL,
      
ResourceSubType             VARCHAR(100) NULL,
      
ResourceDescription         VARCHAR(100) NULL,
       
ResourceAssociatedEntityID  BIGINT,
      
RequestType                 VARCHAR(100) NULL,
      
RequestMode                 VARCHAR(100) NULL,
      
RequestOwnerType            VARCHAR(100) NULL,
      
BlockingSessionID           INT,
       
WaitType                    VARCHAR(100) NULL,
       
WaitDurationMS              BIGINT
   )
AS
/***************************************************************
Purpose:   To retrieve details of all currently blocked lock
           requests, the resources that requested them and the
           session that is blocking them
          
           Scope of the results is for the server unless a DatabaseName is provided
          
           The following DMVs are used:
               sys.dm_tran_locks   - contains a record of all active locks on the server
               dm_os_waiting_tasks - contains a record of all waiting tasks ( there may be many
                                       tasks associates with one sesion)
          
          
Author:        Unknown
History:   7 May 2012 - Initial Issue  
           1 July 2012 - ChillyDBA - Converted to a table valued function

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

BEGIN

   INSERT
@BlockingLocks
      
(
          
RequestSessionID,
          
DatabaseName,
          
ResourceType,
          
ResourceSubType,
          
ResourceDescription,
          
ResourceAssociatedEntityID,
          
RequestType,
          
RequestMode,
          
RequestOwnerType,
          
BlockingSessionID,
          
WaitType,
          
WaitDurationMS
      
)
      
  
-- Look for blocking
  
SELECT
          
tl.request_session_id               AS RequestSessionID,
          
DB_NAME(tl.resource_database_id)    AS DatabaseName,
          
tl.resource_type                    AS ResourceType,
          
tl.resource_subtype                 AS ResourceSubType,
          
tl.resource_description             AS ResourceDescription,
          
tl.resource_associated_entity_id    AS ResourceAssociatedEntityID,
          
request_type                        AS RequestType,
          
request_mode                        AS RequestMode,
          
request_owner_type                  AS RequestOwnerType,
          
wt.blocking_session_id              AS BlockingSessionID,
          
wt.wait_type                        AS WaitType,
          
wt.wait_duration_ms                 AS WaitDurationMS
  
FROM    sys.dm_tran_locks AS tl
          
INNER JOIN sys.dm_os_waiting_tasks AS wt
              
ON tl.lock_owner_address = wt.resource_address
  
WHERE tl.resource_database_id = ISNULL(DB_ID(@DatabaseName), tl.resource_database_id)
  
ORDER BY wait_duration_ms DESC ;
  
  
RETURN
END


Test Code:


SELECT * FROM dbo.udf_GetBlockingLocks(NULL)
SELECT * FROM dbo.udf_GetBlockingLocks('AdventureWorks')


ċ
udf_GetBlockingLocks.sql
(3k)
Andy Hughes,
Jul 1, 2012, 10:31 AM
Comments