Wednesday, May 10, 2017

SQL Server: table & index names from sys.dm_tran_locks

Martin Smith gave a great answer on StackOverflow - as he often does - when someone was looking to get a table name from the information in sys.dm_tran_locks.
http://stackoverflow.com/a/7821066

Here's my take at an answer - trying to cut down on the rows returned to keep the list short in busy databases.


;WITH tLock_cte(request_session_id, dbName, tableName, indexName, resource_type, request_mode) AS
(SELECT tL.request_session_id,
        db_name(tL.resource_database_id) AS dbName,
        CASE WHEN tL.resource_database_id <> db_id() THEN '[in another database]' 
             WHEN tL.resource_type = 'OBJECT' 
                  THEN object_name(tL.resource_associated_entity_id)
             WHEN tL.resource_type = 'ALLOCATION_UNIT' THEN object_name(sp3.[object_id])
             WHEN au1.type = 0 THEN 'DEFERRED DROP'
             WHEN au1.type = 2 THEN object_name(sp2.[object_id])
             ELSE object_name(sp1.[object_id])
        END       AS tableName,
        si.[name] AS indexName,
        tL.resource_type,
        tL.request_mode
 FROM sys.dm_tran_locks               AS tL
 LEFT OUTER JOIN sys.allocation_units AS au1 
 ON au1.container_id = tL.resource_associated_entity_id
 LEFT OUTER JOIN sys.allocation_units AS au2
 ON au2.allocation_unit_id = tL.resource_associated_entity_id
 LEFT OUTER JOIN sys.partitions       AS sp1
 ON sp1.hobt_id = au1.container_id
 LEFT OUTER JOIN sys.partitions       AS sp2
 ON sp2.[partition_id] = au1.container_id
 LEFT OUTER JOIN sys.partitions       AS sp3
 ON sp3.[partition_id] = au2.container_id
 LEFT OUTER JOIN sys.indexes          AS si 
 ON si.[object_id]  = COALESCE(sp1.[object_id], sp2.[object_id], sp3.[object_id])
    AND si.index_id = COALESCE(sp1.index_id, sp2.index_id, sp3.index_id)
 WHERE tL.resource_associated_entity_id IS NOT NULL
 AND   tL.resource_type IN ('OBJECT', 'PAGE', 'RID', 'KEY',
                            'EXTENT', 'ALLOCATION_UNIT', 'HOBT'))
SELECT   request_session_id, dbName, tableName, indexName, resource_type, request_mode
FROM     tLock_cte
GROUP BY request_session_id, dbName, tableName, indexName, resource_type, request_mode;

Let's give it a test.


USE LKN
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
DROP TABLE IF EXISTS test_locks;
CREATE TABLE test_locks
(num BIGINT);
GO
BEGIN TRAN
CREATE INDEX nci_test ON test_locks(num);
INSERT INTO test_locks SELECT 1;
DELETE FROM test_locks WHERE 1 = 0;

--COMMIT TRAN

The results...


Good enough for now.

So let's commit the transaction in order to release the locks.  And I'll have more for you another day...

COMMIT TRAN



No comments:

Post a Comment