Browse cheat sheets · SQL Server

Technology: SQL ServerCategory: TroubleshootingDifficulty: IntermediateUpdated

Inspect a SQL Server Session

Inspect a SQL Server session in detail, including request state, waits, transaction context, client metadata, SQL text, and execution-plan handles.

On this page
  1. Problem
  2. Quick answer
  3. Code / commands
  4. 1. Inspect core session and request details
  5. 2. Show the currently executing statement inside the batch
  6. 3. Check whether the session is blocked or blocking someone else
  7. 4. Inspect open transaction context
  8. 5. Show the last submitted command for a sleeping session
  9. 6. Inspect connection-level metadata
  10. 7. Retrieve the most recent SQL text from the connection handle
  11. 8. Retrieve the active execution plan handle and plan XML
  12. What the result means
  13. What to look for
  14. Session is sleeping with open_transaction_count > 0
  15. Request is suspended
  16. Request is runnable
  17. High CPU with relatively low elapsed time
  18. High elapsed time with low CPU
  19. Unexpected program_name or host_name
  20. Old transaction start time
  21. Production notes
  22. Session and request are different concepts
  23. Permissions vary by SQL Server version
  24. SQL text and client metadata may be sensitive
  25. Runtime metrics belong to the current request
  26. The execution plan you see may not explain historical behavior
  27. Common mistakes
  28. 1. Assuming a sleeping session is harmless
  29. 2. Reading session CPU as current request CPU
  30. 3. Treating DBCC INPUTBUFFER as a complete history
  31. 4. Killing the session before checking transactions
  32. 5. Trusting host_name or program_name as security evidence
  33. 6. Looking only at the query text
  34. Next step
  35. Related deep dives

Problem

You already know the session_id (SPID) you care about and need to understand exactly what that connection is doing.

The useful questions are:

  • Is the session active or sleeping?
  • Does it currently have a request?
  • What SQL is executing?
  • What is it waiting on?
  • Is it blocked?
  • Does it have an open transaction?
  • Which login, host, and application own the connection?
  • How much CPU, I/O, and elapsed time has the current request accumulated?

This is the drill-down step after a broader query has identified a suspicious session.

Quick answer

Start with sys.dm_exec_sessions, left join the active request from sys.dm_exec_requests, and pull SQL text with sys.dm_exec_sql_text.

DECLARE @session_id int = 57;

SELECT
    s.session_id,
    s.status AS session_status,
    s.login_name,
    s.original_login_name,
    s.host_name,
    s.program_name,
    s.client_interface_name,
    s.login_time,
    s.last_request_start_time,
    s.last_request_end_time,
    s.open_transaction_count,

    r.status AS request_status,
    r.command,
    DB_NAME(r.database_id) AS database_name,
    r.start_time AS request_start_time,
    CAST(r.total_elapsed_time / 1000.0 AS decimal(18,1)) AS elapsed_seconds,
    r.cpu_time AS cpu_ms,
    r.logical_reads,
    r.reads,
    r.writes,
    r.wait_type,
    r.wait_time AS wait_ms,
    r.wait_resource,
    r.blocking_session_id,

    txt.text AS batch_text,
    r.plan_handle
FROM sys.dm_exec_sessions AS s
LEFT JOIN sys.dm_exec_requests AS r
    ON r.session_id = s.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS txt
WHERE
    s.session_id = @session_id;

If the session is active, this gives you the current request. If it is sleeping, request columns will be NULL, which is itself useful information.

Code / commands

1. Inspect core session and request details

Use this as the default drill-down query.

DECLARE @session_id int = 57;

SELECT
    s.session_id,
    s.status AS session_status,
    s.login_name,
    s.original_login_name,
    s.host_name,
    s.program_name,
    s.client_interface_name,
    s.login_time,
    s.last_request_start_time,
    s.last_request_end_time,
    s.open_transaction_count,

    r.status AS request_status,
    r.command,
    DB_NAME(r.database_id) AS database_name,
    r.start_time,
    r.total_elapsed_time,
    r.cpu_time,
    r.logical_reads,
    r.reads,
    r.writes,
    r.wait_type,
    r.wait_time,
    r.wait_resource,
    r.blocking_session_id,

    txt.text AS batch_text,
    r.sql_handle,
    r.plan_handle
FROM sys.dm_exec_sessions AS s
LEFT JOIN sys.dm_exec_requests AS r
    ON r.session_id = s.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS txt
WHERE
    s.session_id = @session_id;

This intentionally uses a LEFT JOIN. A sleeping session has no active request row, and you still want to see the session metadata.


2. Show the currently executing statement inside the batch

When a stored procedure or multi-statement batch is running, the whole batch is often too much context.

DECLARE @session_id int = 57;

SELECT
    r.session_id,
    r.status,
    r.command,
    DB_NAME(r.database_id) AS database_name,
    r.start_time,
    CASE
        WHEN r.statement_start_offset IS NULL THEN txt.text
        ELSE SUBSTRING(
            txt.text,
            (r.statement_start_offset / 2) + 1,
            (
                (
                    CASE r.statement_end_offset
                        WHEN -1 THEN DATALENGTH(txt.text)
                        ELSE r.statement_end_offset
                    END
                    - r.statement_start_offset
                ) / 2
            ) + 1
        )
    END AS statement_text,
    txt.text AS batch_text
FROM sys.dm_exec_requests AS r
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS txt
WHERE
    r.session_id = @session_id;

Use statement_text to focus on what is executing now, while keeping batch_text for context.


3. Check whether the session is blocked or blocking someone else

DECLARE @session_id int = 57;

SELECT
    r.session_id,
    r.blocking_session_id,
    r.wait_type,
    r.wait_time AS wait_ms,
    r.wait_resource,
    r.status,
    r.command
FROM sys.dm_exec_requests AS r
WHERE
    r.session_id = @session_id

UNION ALL

SELECT
    r.session_id,
    r.blocking_session_id,
    r.wait_type,
    r.wait_time AS wait_ms,
    r.wait_resource,
    r.status,
    r.command
FROM sys.dm_exec_requests AS r
WHERE
    r.blocking_session_id = @session_id;

Interpret the first result set as “is this session blocked?” and the second as “is this session blocking active requests?”

For a full chain, use the dedicated blocking cheat sheet.


4. Inspect open transaction context

Use this when the session is sleeping, blocking others, or appears to hold locks after its request finished.

DECLARE @session_id int = 57;

SELECT
    s.session_id,
    s.status,
    s.open_transaction_count,
    st.transaction_id,
    at.name AS transaction_name,
    at.transaction_begin_time,
    at.transaction_type,
    at.transaction_state
FROM sys.dm_exec_sessions AS s
LEFT JOIN sys.dm_tran_session_transactions AS st
    ON st.session_id = s.session_id
LEFT JOIN sys.dm_tran_active_transactions AS at
    ON at.transaction_id = st.transaction_id
WHERE
    s.session_id = @session_id;

A sleeping session with an old active transaction is an important signal during blocking incidents.


5. Show the last submitted command for a sleeping session

If there is no active request, sys.dm_exec_requests cannot show current SQL text.

For a targeted session:

DBCC INPUTBUFFER (57);

Use this to get the last submitted statement for context.

Do not assume the returned statement is necessarily the exact statement that acquired every lock the session still holds.


6. Inspect connection-level metadata

Use sys.dm_exec_connections when you need network/protocol details or the most recent SQL handle associated with the connection.

DECLARE @session_id int = 57;

SELECT
    c.session_id,
    c.connect_time,
    c.net_transport,
    c.protocol_type,
    c.auth_scheme,
    c.client_net_address,
    c.local_net_address,
    c.local_tcp_port,
    c.most_recent_sql_handle
FROM sys.dm_exec_connections AS c
WHERE
    c.session_id = @session_id;

Use this when ownership or connection source is unclear.

Be careful when sharing network addresses in tickets or public examples.


7. Retrieve the most recent SQL text from the connection handle

This can help when the session has no active request but the connection still exposes a recent SQL handle.

DECLARE @session_id int = 57;

SELECT
    c.session_id,
    txt.text AS most_recent_batch_text
FROM sys.dm_exec_connections AS c
OUTER APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) AS txt
WHERE
    c.session_id = @session_id;

Treat this as recent connection context, not a guaranteed record of what is currently executing.


8. Retrieve the active execution plan handle and plan XML

If the request is currently active and has a plan handle:

DECLARE @session_id int = 57;

SELECT
    r.session_id,
    r.plan_handle,
    qp.query_plan
FROM sys.dm_exec_requests AS r
OUTER APPLY sys.dm_exec_query_plan(r.plan_handle) AS qp
WHERE
    r.session_id = @session_id;

Use the execution plan to understand operators, scans, joins, estimates, memory use, and other plan-level behavior.

Do not make a tuning decision from one operator alone. Read the plan in the context of runtime symptoms and workload.

What the result means

Field Why it matters
session_status State of the connection/session. A session can be sleeping while still holding an open transaction.
request_status State of the current request, if one exists. Common values include running, runnable, and suspended.
login_name Login currently associated with the session.
original_login_name Original login identity before any execution-context changes.
host_name Client host reported by the connection.
program_name Client application name reported by the connection.
last_request_start_time When the session’s most recent request started.
last_request_end_time When its most recent completed request ended.
open_transaction_count Number of open transactions associated with the session.
blocking_session_id Session blocking the current request, when applicable.
wait_type Current wait category for an active request.
wait_resource Resource tied to the current wait when available.
logical_reads Buffer-pool page reads accumulated by the current request.
cpu_time CPU milliseconds accumulated by the current request.
plan_handle Handle you can use to inspect the current cached execution plan.
most_recent_sql_handle SQL handle associated with the most recent request on the connection.

What to look for

Session is sleeping with open_transaction_count > 0

This is high-value evidence.

Possible causes include:

  • application transaction left open
  • error path skipped commit/rollback
  • manual transaction left uncommitted
  • connection returned to a pool with transaction state still open

If the same session is blocking others, investigate the transaction lifecycle before doing anything destructive.

Request is suspended

Look at:

  • wait_type
  • wait_time
  • wait_resource
  • blocking_session_id

A suspended request is waiting on something. The wait category usually tells you where to go next.

Request is runnable

The request is ready to run but waiting for scheduler CPU time.

If many requests are runnable and server CPU is high, investigate CPU pressure rather than treating the session as a blocking problem.

High CPU with relatively low elapsed time

The request may be actively CPU-intensive.

Inspect the execution plan and compare it with other active workload before deciding it is the root cause.

High elapsed time with low CPU

The request may be spending most of its time waiting.

Look at current waits and blocking.

Unexpected program_name or host_name

This can reveal an unanticipated workload source:

  • reporting tool
  • scheduled job
  • API service
  • ad hoc SSMS session
  • application server
  • ETL process

Use this to identify ownership before changing or terminating anything.

Old transaction start time

An old open transaction can explain:

  • prolonged blocking
  • lock retention
  • log truncation issues in some scenarios
  • unexpected concurrency pressure

Investigate the application or process that owns the transaction.

Production notes

Session and request are different concepts

sys.dm_exec_sessions describes the connection/session.

sys.dm_exec_requests describes currently executing requests.

A sleeping session can exist without a current request. That is why a LEFT JOIN is important in session-detail tooling.

Permissions vary by SQL Server version

For SQL Server 2019 and earlier, broad visibility into sessions/requests generally requires VIEW SERVER STATE.

For SQL Server 2022 and later, the corresponding permission is VIEW SERVER PERFORMANCE STATE.

Azure SQL Database uses different scope and permission rules.

SQL text and client metadata may be sensitive

Session inspection can expose:

  • SQL literals
  • customer identifiers
  • hostnames
  • client IP information
  • application names
  • login names

Sanitize output before placing it in public tickets, documentation, or chat systems.

Runtime metrics belong to the current request

Fields such as CPU, reads, writes, wait state, and elapsed time from sys.dm_exec_requests describe the active request.

They are not lifetime totals for the session.

The execution plan you see may not explain historical behavior

An active request plan is valuable, but production performance can vary with:

  • parameter values
  • recompilation
  • plan changes
  • concurrency
  • memory grants
  • data distribution
  • workload timing

Use Query Store or persisted monitoring when history matters.

Common mistakes

1. Assuming a sleeping session is harmless

Sleeping only means the session has no active request.

It can still own an open transaction and hold locks.

2. Reading session CPU as current request CPU

Session-level and request-level counters answer different questions.

Use sys.dm_exec_requests when diagnosing the currently executing request.

3. Treating DBCC INPUTBUFFER as a complete history

It gives recent statement context, not a complete timeline of every command the session executed.

4. Killing the session before checking transactions

A session with a large open transaction may require significant rollback work.

Understand the transaction first.

5. Trusting host_name or program_name as security evidence

These fields are client-supplied metadata.

They are useful operational clues, not a security boundary.

6. Looking only at the query text

Query text tells you what was submitted.

It does not tell you:

  • whether the request is blocked
  • how much CPU it used
  • what it is waiting on
  • whether the session owns a transaction
  • whether the plan is efficient

Use the session, request, transaction, and plan context together.

Next step

Use the session details to choose the next path.

  • blocking_session_id > 0 → move to blocking-chain analysis.
  • Session is blocking others → inspect open transactions and identify the head blocker.
  • Session is sleeping with an open transaction → investigate transaction lifecycle and application behavior.
  • High CPU → inspect the execution plan and high-CPU workload.
  • High logical reads → inspect access paths, predicates, and indexing.
  • Meaningful wait type → investigate the relevant wait category.
  • No active request but you need history → use Query Store, Extended Events, or persisted monitoring.
  • Unexpected owner/application → trace the workload back through login, host, program, and connection metadata.

← All cheat sheets