DBA Scripts: Get Active Sessions and Requests

---

Who’s Connected, and What’s Actually Running Right Now

Worker Threads and Active Sessions gives you the aggregate health number: how close the worker thread pool is to exhaustion. These three scripts give you the per-session detail underneath that number. Get-ActiveSessions lists every user session, connected or idle, with its current wait type and statement. Get-ActiveRequests narrows to just sessions with a request actively in flight right now, ordered so head blockers surface first. Get-ActiveRequestsWithPlan is the same as Get-ActiveRequests with the execution plan attached, for when you need to see not just what’s running but how.

---

Why Active Sessions and Requests Matter

• A session count spiking unexpectedly, or a specific login/program showing far more connections than usual, is often the first visible sign of a connection leak or a runaway application retry loop
• open_transaction_count > 0 on an idle session is a real, common problem, a connection holding a transaction open with no active request blocks other work and doesn’t show up in a simple “what’s running” check
• Ordering active requests by blocking relationship first (head blocker, then victims) turns “what’s running” into “what’s actually causing the problem” in one glance
• The execution plan attached to a live, currently-running query is more useful than a cached plan for genuinely long-running or currently-stuck requests, since it reflects exactly what’s happening right now

---

When to Run These Scripts

• Get-ActiveSessions — general session-level investigation, connection count anomalies, or confirming what’s actually connected before troubleshooting further
• Get-ActiveRequests — narrowing straight to what’s actively executing, especially during a performance incident
• Get-ActiveRequestsWithPlan — once a specific long-running or blocking request has been identified and you need its actual execution plan
• Alongside Worker Threads and Active Sessions when the aggregate number looks concerning and you need the per-session breakdown behind it

---

The Scripts

1. Get-ActiveSessions — Every User Session, Connected or Idle

/*
Script Name : Get-ActiveSessions
Category    : performance-troubleshooting
Purpose     : Show all active user sessions with current wait type, blocking, elapsed time, and statement.
Author      : Peter Whyte (https://sqldba.blog/dba-scripts-get-active-sessions-and-requests/)
Requires    : VIEW SERVER STATE
HealthCheck : Yes
*/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;

SELECT
    s.session_id,
    s.status AS session_status,
    s.login_name,
    s.host_name,
    s.program_name,
    DB_NAME(s.database_id) AS database_name,
    s.open_transaction_count,
    r.status AS request_status,
    r.wait_type,
    CAST(ISNULL(r.wait_time, 0) / 1000.0 AS DECIMAL(10,2)) AS wait_time_sec,
    r.blocking_session_id,
    CAST(ISNULL(r.total_elapsed_time, 0) / 1000.0 AS DECIMAL(10,2)) AS elapsed_sec,
    r.cpu_time AS cpu_ms,
    r.logical_reads,
    r.writes,
    s.last_request_start_time,
    SUBSTRING(
        ISNULL(qt.text, ''),
        (ISNULL(r.statement_start_offset, 0) / 2) + 1,
        CASE
            WHEN ISNULL(r.statement_end_offset, -1) = -1
                THEN LEN(ISNULL(qt.text, ''))
            ELSE (r.statement_end_offset - r.statement_start_offset) / 2
        END
    ) AS current_statement
FROM sys.dm_exec_sessions AS s
LEFT JOIN sys.dm_exec_requests AS r ON s.session_id = r.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS qt
WHERE s.is_user_process = 1
ORDER BY
    ISNULL(r.blocking_session_id, 0) DESC,
    s.open_transaction_count DESC,
    s.session_id;

2. Get-ActiveRequests — Point-in-Time Snapshot of What’s Actually Running

/*
Script Name : Get-ActiveRequests
Category    : diagnostics
Purpose     : Point-in-time snapshot of all active requests — sessions with a
              request currently in flight. Returns wait type, blocking chain,
              CPU, reads, writes, elapsed time, TempDB consumption, and the
              current executing statement. Excludes idle sessions and the
              diagnostic session itself.
Author      : Peter Whyte (https://sqldba.blog/dba-scripts-get-active-sessions-and-requests/)
Requires    : VIEW SERVER STATE
*/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;

SELECT
    r.session_id,
    s.login_name,
    s.host_name,
    s.program_name,
    DB_NAME(r.database_id) AS database_name,
    r.status,
    r.wait_type,
    r.wait_time AS wait_time_ms,
    r.blocking_session_id,
    r.cpu_time AS cpu_time_ms,
    r.logical_reads,
    r.writes,
    r.total_elapsed_time AS total_elapsed_time_ms,
    CAST(
        (ISNULL(su.user_objects_alloc_page_count, 0) +
         ISNULL(su.internal_objects_alloc_page_count, 0)) * 8
    AS BIGINT) AS tempdb_allocations_kb,
    CAST(
        (ISNULL(su.user_objects_alloc_page_count, 0) - ISNULL(su.user_objects_dealloc_page_count, 0) +
         ISNULL(su.internal_objects_alloc_page_count, 0) - ISNULL(su.internal_objects_dealloc_page_count, 0)) * 8
    AS BIGINT) AS tempdb_current_kb,
    SUBSTRING(
        ISNULL(qt.text, ''),
        (ISNULL(r.statement_start_offset, 0) / 2) + 1,
        CASE
            WHEN ISNULL(r.statement_end_offset, -1) = -1
                THEN LEN(ISNULL(qt.text, ''))
            ELSE (r.statement_end_offset - r.statement_start_offset) / 2 + 1
        END
    ) AS sql_text
FROM sys.dm_exec_requests AS r
JOIN sys.dm_exec_sessions AS s ON s.session_id = r.session_id
LEFT JOIN sys.dm_db_session_space_usage AS su ON su.session_id = r.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS qt
WHERE s.is_user_process = 1
  AND r.session_id <> @@SPID
ORDER BY
    CASE
        WHEN EXISTS (SELECT 1 FROM sys.dm_exec_requests r2 WHERE r2.blocking_session_id = r.session_id)
            THEN 0 -- head blocker: blocking others, not blocked itself
        WHEN r.blocking_session_id > 0 THEN 1 -- victim: waiting on a blocker
        ELSE 2
    END,
    r.total_elapsed_time DESC;

3. Get-ActiveRequestsWithPlan — Same Snapshot, With the Execution Plan

/*
Script Name : Get-ActiveRequestsWithPlan
Category    : diagnostics
Purpose     : Point-in-time snapshot of all active requests with XML execution
              plans. Same columns as Get-ActiveRequests.sql with the addition of
              query_plan from sys.dm_exec_query_plan. Use the PowerShell wrapper
              to extract plans to individual XML files for SSMS analysis.
Author      : Peter Whyte (https://sqldba.blog/dba-scripts-get-active-sessions-and-requests/)
Requires    : VIEW SERVER STATE
*/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;

SELECT
    r.session_id,
    s.login_name,
    s.host_name,
    s.program_name,
    DB_NAME(r.database_id) AS database_name,
    r.status,
    r.wait_type,
    r.wait_time AS wait_time_ms,
    r.blocking_session_id,
    r.cpu_time AS cpu_time_ms,
    r.logical_reads,
    r.writes,
    r.total_elapsed_time AS total_elapsed_time_ms,
    CAST(
        (ISNULL(su.user_objects_alloc_page_count, 0) +
         ISNULL(su.internal_objects_alloc_page_count, 0)) * 8
    AS BIGINT) AS tempdb_allocations_kb,
    CAST(
        (ISNULL(su.user_objects_alloc_page_count, 0) - ISNULL(su.user_objects_dealloc_page_count, 0) +
         ISNULL(su.internal_objects_alloc_page_count, 0) - ISNULL(su.internal_objects_dealloc_page_count, 0)) * 8
    AS BIGINT) AS tempdb_current_kb,
    SUBSTRING(
        ISNULL(qt.text, ''),
        (ISNULL(r.statement_start_offset, 0) / 2) + 1,
        CASE
            WHEN ISNULL(r.statement_end_offset, -1) = -1
                THEN LEN(ISNULL(qt.text, ''))
            ELSE (r.statement_end_offset - r.statement_start_offset) / 2 + 1
        END
    ) AS sql_text,
    CAST(qp.query_plan AS NVARCHAR(MAX)) AS query_plan
FROM sys.dm_exec_requests AS r
JOIN sys.dm_exec_sessions AS s ON s.session_id = r.session_id
LEFT JOIN sys.dm_db_session_space_usage AS su ON su.session_id = r.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS qt
OUTER APPLY sys.dm_exec_query_plan(
    CASE WHEN r.plan_handle <> 0x0000000000000000000000000000000000000000
         THEN r.plan_handle END
) AS qp
WHERE s.is_user_process = 1
  AND r.session_id <> @@SPID
ORDER BY
    CASE
        WHEN EXISTS (SELECT 1 FROM sys.dm_exec_requests r2 WHERE r2.blocking_session_id = r.session_id)
            THEN 0 -- head blocker: blocking others, not blocked itself
        WHEN r.blocking_session_id > 0 THEN 1 -- victim: waiting on a blocker
        ELSE 2
    END,
    r.total_elapsed_time DESC;

---

How To Run From The Repo

Clone DBA Tools, initialize and run whichever script answers your question:

# Clone dba-tools repo:
git clone https://github.com/peterwhyte-lgtm/dba-tools

# Initialize environment:
cd dba-tools
.\Initialize-Environment.ps1

# Every user session, connected or idle:
.\run.ps1 Get-ActiveSessions

# Just sessions with a request actively running:
.\run.ps1 Get-ActiveRequests

# Same as above, with the execution plan attached:
.\run.ps1 Get-ActiveRequestsWithPlan

# Any of these against a remote sql server:
.\run.ps1 Get-ActiveRequests -ServerInstance SQLSERVER01

These scripts live in the repo at:

• sql/performance/active-sessions/Get-ActiveSessions.sql
• sql/performance/active-sessions/Get-ActiveRequests.sql
• sql/performance/active-sessions/Get-ActiveRequestsWithPlan.sql

---

Example Output

Get-ActiveSessions (7 sessions, mostly idle SQL Agent subsystems and one active SSMS connection):

session_id	session_status	login_name	program_name	open_transaction_count
59	running	PWSQL01\Peter	Core Microsoft SqlClient Data Provider	0
61	sleeping	NT SERVICE\SQLSERVERAGENT	SQLAgent – Job invocation engine	0
84	sleeping	PWSQL01\Peter	SQL Server Management Studio	0

Get-ActiveRequests and Get-ActiveRequestsWithPlan both return 0 rows here, no request was actively executing at the exact instant this point-in-time snapshot ran, an honest, valid result for a quiet lab instance.

---

Understanding the Results

• session_status = sleeping with open_transaction_count > 0 — a session holding a transaction open with nothing actively running, this is the pattern behind Open Transactions findings and a common, avoidable blocking cause
• 0 rows from Get-ActiveRequests — don’t mistake this for “nothing is happening,” it means no request was mid-execution at that exact instant; a session can still be connected and about to run something
• blocking_session_id populated, ordered first — Get-ActiveRequests and its WithPlan variant both order head blockers to the top specifically so the actual root cause of a blocking chain is the first row you see, not buried in the middle of a long list
• query_plan is NULL for a session with no cached plan yet — expected for a request still in parse/compile, or an ad-hoc statement that hasn’t been assigned a reusable plan

---

Best Practices

• Run Get-ActiveSessions first for the full picture, then narrow to Get-ActiveRequests once you know which specific session needs closer attention
• Reach for Get-ActiveRequestsWithPlan only once you’ve identified a specific problem request, plan capture adds overhead not worth paying for a routine check
• Watch for open_transaction_count > 0 on sleeping sessions as its own finding, independent of anything currently executing
• Pair with Worker Threads and Active Sessions when the aggregate thread-pool number looks concerning and you need the specific sessions behind it

---

Related Scripts

You may also find these scripts useful:

• Worker Threads and Active Sessions
• Blocking Chains
• Open Transactions
• Lock Escalation, Contention Analysis, and Blocking Chains with Plan
• DBA Scripts: The Complete Guide, the map across every script on this site

---

Frequently Asked Questions

Summary

“What’s connected” and “what’s actually running right now” are related but different questions, and a performance investigation often needs both. These three scripts cover the range, from a full session list to a point-in-time execution snapshot to that same snapshot with the plan attached for deeper analysis.

Run Get-ActiveSessions for the full picture, and reach for Get-ActiveRequests and its WithPlan variant once you’ve narrowed down to something specific that needs a closer look.