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 3 scripts give you the per-session detail underneath that number. Get-ActiveSessions lists every user session, idle or running, with its current wait type and statement. That includes SQL Agent’s own connections. 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 that spikes, or one login or program holding far more connections than usual, is often the first visible sign of a connection leak or an application stuck in a retry loop.
• open_transaction_count above 0 on a sleeping session is a common, real problem. The connection holds a transaction open with nothing running, blocks other work, and never shows up in a simple “what is running” check.
• Ordering active requests head blocker first, then the sessions it blocks, turns “what is running” into “what is causing the problem” at a glance.
• The plan attached to a request that is running now shows how that query is executing at this moment, which is what you need for a request that is stuck or running long.

When to Run These Scripts

• Get-ActiveSessions: a general look at what is connected, a connection count that looks wrong, or a quick check before troubleshooting further.
• Get-ActiveRequests: straight to what is executing, especially mid incident.
• Get-ActiveRequestsWithPlan: once you have found the request that is running long or blocking, and need its plan.
• Alongside Worker Threads and Active Sessions when the aggregate number looks wrong and you need the sessions 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.
              query_plan is returned as xml, so in SSMS it is a link that opens
              the graphical plan.
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,
    qp.query_plan 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 on a lab instance under memory pressure, 13 sessions:

The 4 Agent job steps at the top have been suspended on RESOURCE_SEMAPHORE for nearly 4 minutes: they are queued for a memory grant, not blocked, so blocking_session_id reads 0. Session 54 is the SSMS window running this script, which Get-ActiveSessions does not filter out. The sleeping rows have no request, so their request columns are NULL.

Get-ActiveRequests on the same instance a few minutes later, condensed. The first 2 job steps have now waited 12 minutes for memory:

No row is blocked, so the ordering falls back to longest elapsed first. When the top rows all wait on RESOURCE_SEMAPHORE, work through RESOURCE_SEMAPHORE When SQL Server Is Nowhere Near Its Memory Cap.

Understanding the Results

Get-ActiveSessions

Get-ActiveRequests and Get-ActiveRequestsWithPlan

Same identity columns as above (session_id, login_name, host_name, program_name, database_name), plus:

Best Practices

• Run Get-ActiveSessions first for the full picture, then Get-ActiveRequests once you know which session needs a closer look.
• Use Get-ActiveRequestsWithPlan once you have a specific request in mind. Each row carries a plan that can run past 100 KB of XML, which is a lot to pull for a routine check.
• Treat open_transaction_count above 0 on a sleeping session as a finding in its own right, whatever else is running.
• Pair with Worker Threads and Active Sessions when the thread pool number looks wrong and you need the sessions behind it.

Microsoft’s reference covers sys.dm_exec_requests, sys.dm_exec_sessions and sys.dm_exec_sql_text in full.

Related Scripts

You may also find these scripts useful:

• Worker Threads and Active Sessions
• SQL Server Is Slow: What to Check First, in Order, the incident walkthrough where this output gets read
• The Query Is Fast in SSMS but Slow From the Application, when the same query behaves differently from the application and from SSMS
• Blocking Chains
• Open Transactions
• Lock Escalation, Contention Analysis, and Blocking Chains with Plan
• DBA Scripts: Get Long-Running Queries
• 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 3 scripts cover both: a full session list, a snapshot of what is executing, and the same snapshot with the plan attached.

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.