Skip to content

Mr SQL's Blog

Musings on Microsoft SQL Server

Tag Archives: DMV

Locking occurs frequently within a database and is a desired feature for a relational database system. SQL Server places locks to allow the sharing of resources (tables, pages, rows etc) when multiple users attempt to access or alter the same data.

However, locking can become problematic when many users (or processes) are constantly trying to access the same data. This can lead to contention and deadlocking. The following DMV query can assist with the troubleshooting of such issues:-

SELECT  L.request_session_id AS 'SPID', 
        DB_NAME(L.resource_database_id) AS 'Database Name',
        O.Name AS 'LockedObjectName', 
        P.object_id AS 'LockedObjectId', 
        L.resource_type AS 'LockedResource', 
        L.request_mode AS 'LockType',
        ST.text AS 'SqlStatementText',        
        ES.login_name AS 'LoginName',
        ES.host_name AS 'HostName',
        TST.is_user_transaction as 'IsUserTransaction',
        AT.name as 'TransactionName',
        CN.auth_scheme as 'AuthenticationMethod'
FROM    sys.dm_tran_locks L
        JOIN sys.partitions P ON P.hobt_id = L.resource_associated_entity_id
        JOIN sys.objects O ON O.object_id = P.object_id
        JOIN sys.dm_exec_sessions ES ON ES.session_id = L.request_session_id
        JOIN sys.dm_tran_session_transactions TST ON ES.session_id = TST.session_id
        JOIN sys.dm_tran_active_transactions AT ON TST.transaction_id = AT.transaction_id
        JOIN sys.dm_exec_connections CN ON CN.session_id = ES.session_id
        CROSS APPLY sys.dm_exec_sql_text(CN.most_recent_sql_handle) AS ST
WHERE   resource_database_id = db_id()
ORDER BY L.request_session_id;

The query outputs the SPID that obtained the lock, the database affected, the object (table), the resource affected (e.g. PAGE, KEY etc), the SQL statement that was executed, login name of the user, the host name, whether the query is a user statement and the authentication method. By identifying long running queries and data that is being constantly locked, problems can be understood and solutions can be found to improve performance.

Advertisements

Tags: , , ,

%d bloggers like this: