sys.dm_tran_locks query gets hung - what could be the cause?

  • I'm in the process of troubleshooting a blocking issue on a production server.

    However, the following query does not seem to complete in a reasonable amount of time (1-2 minutes).

    I wonder what the reason of that might be; should I wait longer for it to complete?

    I'm concerned about the impact on application performance of having it running for too long.

    Here is the query:

    --Blocking

    --http://www.simple-talk.com/sql/database-administration/investigating-transactions-using-dynamic-management-objects/

    SELECT

    L.[request_session_id] AS [session_id] ,

    DB_NAME(L.[resource_database_id]) AS [Database] ,

    L.resource_type ,

    CASE

    WHEN L.resource_type IN ( 'DATABASE', 'FILE', 'METADATA' )

    THEN L.resource_type

    WHEN L.resource_type = 'OBJECT'

    THEN OBJECT_NAME(L.resource_associated_entity_id, L.[resource_database_id])

    WHEN L.resource_type IN ( 'KEY', 'PAGE', 'RID' )

    THEN (SELECT OBJECT_NAME([object_id])

    FROM sys.partitions

    WHERE sys.partitions.hobt_id =

    L.resource_associated_entity_id

    )

    ELSE 'Unidentified'

    END AS [Parent Object] ,

    L.request_mode AS [Lock Type] ,

    L.request_status AS [Request Status] ,

    R.[blocking_session_id] ,

    S.[login_name] ,

    CASE L.request_lifetime

    WHEN 0 THEN SQL_R.TEXT

    ELSE SQL_C.TEXT

    END AS [Statement]

    FROM

    sys.dm_tran_locks L

    LEFT JOIN

    sys.[dm_exec_requests] R

    ON

    L.[request_session_id] = R.[session_id]

    INNER JOIN

    sys.dm_exec_sessions S

    ON

    L.request_session_id = S.[session_id]

    INNER JOIN

    sys.dm_exec_connections C

    ON

    L.[request_session_id] = C.[most_recent_session_id]

    OUTER APPLY

    sys.dm_exec_sql_text (C.[most_recent_sql_handle]) AS SQL_C

    OUTER APPLY

    sys.dm_exec_sql_text(R.[sql_handle]) AS SQL_R

    WHERE

    --L.[resource_database_id] = DB_ID() AND

    L.[resource_type] NOT IN ( 'DATABASE', 'METADATA' )

    ORDER BY

    L.[request_session_id] ;

    __________________________________________________________________________________
    SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
    Persisting SQL Server Index-Usage Statistics with MERGE[/url]
    Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

  • might want to put with nolock on it

    so it doesnt lock itself

    or readuncommitted

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply