Locks

Useful if blocked sessions are held up by lock waits.

--/*
DROP TABLE IF EXISTS #locks
 
CREATE TABLE #locks (
  [spid] SMALLINT
, [dbid] SMALLINT
, [ObjId] INT
, [IndId] SMALLINT
, [Type] NVARCHAR(4)
, [Resource] NVARCHAR(32)
, [Mode] NVARCHAR(8)
, [Status] NVARCHAR(5)
)
 
INSERT INTO #locks (
  [spid]
, [dbid]
, [ObjId] 
, [IndId] 
, [Type]
, [Resource]
, [Mode]
, [Status]
)
--EXEC sp_lock
SELECT
  request_session_id
, resource_database_id
, IIF(resource_type = 'OBJECT', resource_associated_entity_id, 0)
, NULL  -- IndId
, NULL  -- [Type]
, resource_description
, request_mode
, request_status
FROM sys.dm_tran_locks
 
--*/
SELECT 
  DB_NAME([dbid])
, OBJECT_SCHEMA_NAME([ObjId], [dbid])
, OBJECT_NAME([ObjId], [dbid])
, *
FROM #locks
WHERE [ObjId] > 0
--AND [spid] IN (
--  SELECT blocking_session_id
--  FROM sys.dm_os_waiting_tasks
--  WHERE blocking_session_id <> session_id
--)
--GROUP BY   
--  DB_NAME([dbid])
--, OBJECT_SCHEMA_NAME([ObjId], [dbid])
--, OBJECT_NAME([ObjId], [dbid])