Show Current SQL Server Sessions

get-current-sql-sessions.sql

-- Query to display current sessions and their details
-- Displays session details such as host, program, blocking session, CPU time, waits, and SQL text
SELECT 
     s.host_name, 
     s.program_name, 
     r.blocking_session_id AS 'Blk by', 
     COALESCE(
         QUOTENAME(DB_NAME(st.dbid)) + N'.' + 
         QUOTENAME(OBJECT_SCHEMA_NAME(st.objectid, st.dbid)) + N'.' + 
         QUOTENAME(OBJECT_NAME(st.objectid, st.dbid)), 
         ''
     ) AS command_text, -- Full object name from DB, schema, and object
     s.session_id, 
     r.cpu_time, 
     r.logical_reads, 
     r.reads, 
     r.status, 
     r.wait_type, 
     r.wait_resource, 
     r.wait_time / (1000.0) AS 'Wait (in sec)', 
     r.writes, 
     r.total_elapsed_time / (1000.0) AS 'Elapsed (in sec)', 
     SUBSTRING(
         st.TEXT, 
         (r.statement_start_offset / 2) + 1, 
         ((CASE r.statement_end_offset 
              WHEN -1 THEN DATALENGTH(st.TEXT) 
              ELSE r.statement_end_offset 
          END - r.statement_start_offset) / 2) + 1
     ) AS statement_text, -- Active statement text
     r.command, 
     s.login_name, 
     s.last_request_end_time, 
     s.login_time, 
     r.open_transaction_count 
FROM sys.dm_exec_sessions AS s 
JOIN sys.dm_exec_requests AS r 
    ON r.session_id = s.session_id 
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st 
ORDER BY r.cpu_time DESC;