SELECT
    r.session_id,
    s.host_name,
    s.program_name,
    s.login_name,
    DB_NAME(r.database_id) AS database_name,
    r.status,
    r.command,
    r.wait_type,
    r.wait_time,
    r.blocking_session_id,
    r.wait_resource,
    r.cpu_time,
    r.total_elapsed_time,
    t.text AS running_sql
FROM sys.dm_exec_requests r
JOIN sys.dm_exec_sessions s
    ON r.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.session_id <> @@SPID
ORDER BY r.total_elapsed_time DESC;


SELECT
    s.session_id,
    s.status AS session_status,
    s.host_name,
    s.program_name,
    s.login_name,
    s.open_transaction_count,

    r.status AS request_status,
    r.command,
    r.wait_type,
    r.wait_time,
    r.blocking_session_id,
    r.wait_resource,
    r.cpu_time,
    r.total_elapsed_time,

    ib.event_info AS last_command,
    txt.text AS current_sql

FROM sys.dm_exec_sessions s

LEFT JOIN sys.dm_exec_requests r
    ON s.session_id = r.session_id

OUTER APPLY sys.dm_exec_input_buffer(s.session_id, NULL) ib

OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) txt

WHERE s.session_id = 51;


SELECT
    tl.request_session_id,
    DB_NAME(tl.resource_database_id) AS database_name,
    tl.resource_type,
    tl.resource_associated_entity_id,
    tl.request_mode,
    tl.request_status,

    CASE
        WHEN tl.resource_type = 'OBJECT'
        THEN OBJECT_NAME(
            tl.resource_associated_entity_id,
            tl.resource_database_id
        )
        ELSE OBJECT_NAME(
            p.object_id,
            tl.resource_database_id
        )
    END AS object_name,

    p.index_id
FROM sys.dm_tran_locks tl
LEFT JOIN sys.partitions p
    ON tl.resource_associated_entity_id = p.hobt_id
WHERE tl.request_session_id = 51;

SELECT
    st.session_id,
    at.transaction_id,
    at.name AS transaction_name,
    at.transaction_begin_time,
    at.transaction_type,
    at.transaction_state
FROM sys.dm_tran_session_transactions st
JOIN sys.dm_tran_active_transactions at
    ON st.transaction_id = at.transaction_id
WHERE st.session_id = 51;