Active sessions and locks

---

-- active requests
SELECT 
    r.session_id,
    se.host_name,
    se.login_name,
    Db_name(r.database_id) AS dbname,
    r.status,
    r.command,
    r.cpu_time, r.total_elapsed_time,
    r.reads, r.logical_reads, r.writes,
    s.text sql_text, p.query_plan query_plan,
    SQL_CURSORSQL.text, SQL_CURSORPLAN.query_plan
FROM sys.dm_exec_requests r
    INNER JOIN sys.dm_exec_sessions se
        ON r.session_id = se.session_id
    OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) s
    OUTER APPLY sys.dm_exec_query_plan(r.plan_handle) p
    OUTER APPLY sys.dm_exec_cursors(r.session_id) AS SQL_CURSORS
    OUTER APPLY sys.dm_exec_sql_text(SQL_CURSORS.sql_handle) AS SQL_CURSORSQL
    LEFT JOIN sys.dm_exec_query_stats AS SQL_CURSORSTATS
        ON SQL_CURSORSTATS.sql_handle = SQL_CURSORS.sql_handle
    OUTER APPLY sys.dm_exec_query_plan(SQL_CURSORSTATS.plan_handle) AS SQL_CURSORPLAN
WHERE  r.session_id <> @@SPID AND se.is_user_process = 1 

---

-- active locks
SELECT  
    L.request_session_id AS SPID, 
    DB_NAME(L.resource_database_id) AS database_name,
    O.Name AS locked_object_name, 
    P.object_id AS locked_objectId, 
    L.resource_type AS locked_resource, 
    L.request_mode AS lock_type,
    ST.text AS sql_statement_text,        
    ES.login_name AS login_name,
    ES.host_name AS [host_name],
    TST.is_user_transaction as is_user_transaction,
    AT.name as transaction_name,
    CN.auth_scheme as authentication_method
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

---