Browse cheat sheets · SQL Server
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
- Problem
- Quick answer
- Code / commands
- 1. Inspect core session and request details
- 2. Show the currently executing statement inside the batch
- 3. Check whether the session is blocked or blocking someone else
- 4. Inspect open transaction context
- 5. Show the last submitted command for a sleeping session
- 6. Inspect connection-level metadata
- 7. Retrieve the most recent SQL text from the connection handle
- 8. Retrieve the active execution plan handle and plan XML
- What the result means
- What to look for
- Session is sleeping with open_transaction_count > 0
- Request is suspended
- Request is runnable
- High CPU with relatively low elapsed time
- High elapsed time with low CPU
- Unexpected program_name or host_name
- Old transaction start time
- Production notes
- Session and request are different concepts
- Permissions vary by SQL Server version
- SQL text and client metadata may be sensitive
- Runtime metrics belong to the current request
- The execution plan you see may not explain historical behavior
- Common mistakes
- 1. Assuming a sleeping session is harmless
- 2. Reading session CPU as current request CPU
- 3. Treating DBCC INPUTBUFFER as a complete history
- 4. Killing the session before checking transactions
- 5. Trusting host_name or program_name as security evidence
- 6. Looking only at the query text
- Next step
- 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_typewait_timewait_resourceblocking_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.
Was this helpful?
What could be better?
Thanks — that helps me improve Javi Builds.
Stay in the loop
Get the Javi Builds updates you actually care about.
Check your inbox
I sent you a confirmation link. Your subscription will be active after you confirm it.