The Wiert Corner – irregular stream of stuff

Jeroen W. Pluimers on .NET, C#, Delphi, databases, and personal interests

  • My badges

  • Twitter Updates

  • My Flickr Stream

  • Pages

  • All categories

  • Enter your email address to subscribe to this blog and receive notifications of new posts by email.

    Join 1,880 other followers

SQL query to get the deadlocks in SQL SERVER 2005 and up

Posted by jpluimers on 2013/09/05

Darren Davies answered via SQL query to get the deadlocks in SQL SERVER 2008 – Stack Overflow a while ago listing a great SQL statement by Mladen Prajdić that shows how to do without the deprecated SP_LOCKS and SP_WHO2 (which is undocumented, and slightly different from SP_WHO) or the good old SP_LOCK2.

It is the textual equivalent of the Deadlock Graph, which is part of the SQL Server Profiler.
I like that profiler a lot (read this step-by-step intro if you haven’t used it), but some environments consider it too much power for a developer to use.

The SP_LOCKS documentation directs you to the sys.dm_tran_locks documentation, which is the base of the SQL below. It requires the mostly harmless VIEW SERVER STATE permission.

Finding out what to join in order to get some readable results suited for quick troubleshooting is quite an undertaking.
Mladen did all that, and this is his SQL:

SELECT  L.request_session_id AS SPID,
        DB_NAME(L.resource_database_id) AS DatabaseName,
        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

You can extend the above SQL with an inner join to the sys.dm_os_waiting_tasks view as for instance Jon suggests here to get both the request session ID and the blocking session ID.

It is based on a few Dynamic Management Views (which have the great benefit over the SP_xxxx calls in that you can query them, so you don’t need tricky workarounds). If you want to learn more about DMVs or DMFs, then you should read the DMV/DMF cheat sheet page.

Mladen explains all the details on how he combined these views very well.

–jeroen

via: SQL query to get the deadlocks in SQL SERVER 2008 – Stack Overflow.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

 
%d bloggers like this: