SQL Server

How to Get a SQL Server Session ID and See What It Is Doing

Find your SQL Server session ID, investigate another session, and inspect its request, SQL text, waits, blocking and open transaction state.

By JaviPublished Updated 12 min readBeginner

SPID 123 linked to its session, current request, SQL text, login, host, wait, and blocker details

Short answer: SELECT @@SPID returns your own session ID. For any other session, start from sys.dm_exec_sessions (who: login, host, application, login time), join sys.dm_exec_requests (what it is doing now: status, wait, blocker, statement) and sys.dm_exec_connections (when it connected), and use sys.dm_exec_input_buffer for the last batch a sleeping session sent. Before acting on a session ID, confirm it is still the same connection: IDs are reused after a connection closes.

A SQL Server session ID identifies a client connection. It is the number support teams commonly ask for when they need to connect an application symptom to server activity. The same session can execute many requests over its lifetime, so a session ID tells you where to look, not which statement ran at an earlier time.

Problem

An application owner reports that session 123 is stuck. You need to know who owns the session, where it connected from, whether it is actively executing, which statement is running, what it waits for, whether another session blocks it, and whether it owns an open transaction.

Do not begin with KILL 123. Session IDs are reused after disconnects, and ending a session can initiate a long rollback. Capture and confirm the evidence first.

Quick diagnostic

To return the ID of your current connection, run:

SQL query: Quick diagnosticT-SQL
SELECT @@SPID AS session_id;
T-SQL1 lineRead-only query display. It is not connected to a database.

Each query-window connection normally has its own session ID. Reconnecting can produce another value. An application connection pool keeps and reuses connections, so the session may outlive a single web request.

Which DMV answers which question

Question Source Columns to read
Which session is mine? @@SPID The value itself
Who owns this session? sys.dm_exec_sessions login_name, original_login_name, host_name, program_name, login_time
What is it doing right now? sys.dm_exec_requests status, command, wait_type, blocking_session_id, start_time
When and how did it connect? sys.dm_exec_connections connect_time, client_net_address, most_recent_sql_handle
What did it last send? sys.dm_exec_input_buffer event_info (SQL Server 2014 SP2 and later)
Does it hold a transaction? sys.dm_tran_session_transactions with sys.dm_tran_active_transactions transaction_begin_time

Query: give me everything about session 123

Set the parameter once. The query returns the session even when it is sleeping, and enriches it with a current request and transaction information when those exist.

SQL query: Give me everything about session 123T-SQL
DECLARE @SessionId int = 123;

SELECT
    SYSDATETIMEOFFSET() AS captured_at,
    s.session_id,
    s.login_name,
    s.original_login_name,
    s.host_name,
    s.program_name,
    s.client_interface_name,
    s.login_time,
    c.connect_time,
    c.client_net_address,
    s.last_request_start_time,
    s.last_request_end_time,
    s.status AS session_status,
    DB_NAME(COALESCE(r.database_id, s.database_id)) AS database_name,
    r.request_id,
    r.status AS request_status,
    r.command,
    r.start_time AS request_start_time,
    r.total_elapsed_time AS elapsed_time_ms,
    r.cpu_time AS cpu_time_ms,
    r.logical_reads,
    r.reads AS physical_reads,
    r.writes,
    r.wait_type,
    r.wait_time AS wait_time_ms,
    r.wait_resource,
    NULLIF(r.blocking_session_id, 0) AS blocking_session_id,
    s.open_transaction_count AS session_open_transaction_count,
    r.open_transaction_count AS request_open_transaction_count,
    at.transaction_begin_time,
    at.transaction_type,
    at.transaction_state,
    SUBSTRING(
        sql_text.text,
        (COALESCE(r.statement_start_offset, 0) / 2) + 1,
        CASE
          WHEN r.statement_start_offset IS NULL THEN LEN(sql_text.text)
          ELSE ((CASE r.statement_end_offset
                   WHEN -1 THEN DATALENGTH(sql_text.text)
                   ELSE r.statement_end_offset
                 END - r.statement_start_offset) / 2) + 1
        END
    ) AS current_or_recent_statement,
    sql_text.text AS batch_text,
    ib.event_info AS input_buffer
FROM sys.dm_exec_sessions AS s
LEFT JOIN sys.dm_exec_requests AS r
    ON r.session_id = s.session_id
LEFT JOIN sys.dm_exec_connections AS c
    ON c.session_id = s.session_id
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
OUTER APPLY sys.dm_exec_sql_text(COALESCE(r.sql_handle, c.most_recent_sql_handle)) AS sql_text
OUTER APPLY sys.dm_exec_input_buffer(s.session_id, NULL) AS ib
WHERE s.session_id = @SessionId;
T-SQL60 linesRead-only query display. It is not connected to a database.

The expression for database_name uses the request database when a request is active and otherwise the session’s current database. most_recent_sql_handle and the input buffer can show the most recent batch on a sleeping connection, but they do not prove that text is still executing or that it opened the current transaction.

How to read the result

An active session normally has request values: command, start time, counters, wait, and statement text. A sleeping session has no active request, so those columns are NULL. Sleeping only means the session is not currently executing. It can still own an open transaction, retain locks, and block other sessions.

login_name, host_name, and program_name connect the session to an owner. Host and program values come from the client, so treat them as operational hints. last_request_start_time and last_request_end_time show recent session activity but are not a durable statement history.

blocking_session_id identifies the immediate blocker of an active request. It may not be the head blocker. Follow the chain until you reach a session that is not itself blocked, then inspect that session’s transaction and last or current SQL.

Transaction state values are compact internal codes documented for sys.dm_tran_active_transactions; retain the raw value in incident captures and interpret it with the version’s documentation. The begin time is usually more directly useful: an old transaction combined with blocking or log growth deserves prompt investigation.

What the result may mean

An active session with increasing CPU and reads may be progressing through expensive work. A stable lock wait and blocking ID suggest a dependency rather than slow execution inside the blocked request. A sleeping session with an open transaction can indicate an application began a transaction and then returned control without commit or rollback. That pattern can hold locks and prevent log truncation.

Multiple result rows can appear when a session has multiple active transactions or requests. That is not accidental duplication: the relationships are one-to-many. Do not sum request counters across repeated transaction rows without first separating those grains.

Confirm it is the same session before acting

Session IDs are reused: when a connection closes, its number can be given to the next one, often within seconds on a busy server. A session ID copied from a ticket, a monitoring alert or a query run ten minutes ago may now belong to a different user. Record a fingerprint when you first identify the session (session_id, login_time, host_name, program_name, and the request start_time if it was active) and check it again immediately before any action:

SQL query: Confirm it is the same session before actingT-SQL
-- Values captured when the session was first identified
DECLARE @SessionId   int           = 123,
        @LoginTime   datetime      = '2026-11-06T08:14:03.120',
        @HostName    nvarchar(128) = N'APP-SERVER-01',
        @ProgramName nvarchar(128) = N'Billing API';

SELECT s.session_id, s.login_time, s.host_name, s.program_name, s.login_name
FROM sys.dm_exec_sessions AS s
WHERE s.session_id   = @SessionId
  AND s.login_time   = @LoginTime
  AND s.host_name    = @HostName
  AND s.program_name = @ProgramName;
-- No row: the ID now belongs to another connection, or to none. Stop and re-investigate.
T-SQL13 linesRead-only query display. It is not connected to a database.

During a blocking incident, the SQL Server Blocking Runbook covers when ending the head blocker is justified and how to do it safely. If a session is ended, KILL <session_id> WITH STATUSONLY reports rollback progress without ending anything else.

What to check next

For blocking, switch to the blocking-chain query and identify the head blocker. For high reads or CPU, capture the plan and inspect table size, predicates, estimates, and access methods. For a sleeping session with an old transaction, correlate the connection to application telemetry and transaction-handling code. If the activity already ended, use Query Store, Extended Events, or application tracing rather than expecting session DMVs to reconstruct it.

Common mistakes

  • Assuming session ID 123 still refers to the same connection after it disconnected.
  • Treating a session and its current request as the same lifetime.
  • Calling a sleeping session idle without checking open transactions.
  • Treating the most recent SQL text as proof that it opened an existing transaction.
  • Ending a session before estimating rollback impact and identifying the owner.
  • Depending on undocumented convenience procedures as the primary diagnostic method.

Production notes

Seeing other sessions requires appropriate server-state permission. SQL text may include sensitive data, so control who can capture and share it. DMV state is transient across restarts and failovers, and session state disappears when the connection closes.

The query is intentionally diagnostic and read-only. If ending a session becomes necessary, confirm that the identifier is still the intended connection, notify the workload owner, understand availability consequences, and expect rollback to take time. Killing a session is an operational decision, not a tuning technique.

Continue the performance path

Place this inspection inside the performance tuning workflow. Start with running queries, move to the blocking chain when necessary, follow the SQL Server Blocking Runbook during an active blocking incident, and use Query Store regressions after the live activity has ended. Keep the Session Details, Login, Host and Application Activity and Open Transactions quick references nearby.

Sources

Checked November 2026. Seeing other sessions requires VIEW SERVER STATE, or VIEW SERVER PERFORMANCE STATE on SQL Server 2022 and later.

Tags

  • SQL Server
  • Troubleshooting
  • DMV
  • Blocking