Key DMVs for Requests, Sessions, Connections, Transactions, Locks, and Plans

Learning outcomes

A ServiceHub API request is reported as “hung.” CPU is ordinary, the application has not timed out yet, and the only evidence is a screenshot saying one session is blocked. An observer who immediately kills the blocker might restore service—or might roll back the transaction that is protecting the only correct write. The first observability skill is therefore not memorizing a list of dynamic management views (DMVs). It is learning to ask a narrow question and join the transient pieces of engine state that can answer it.

1. Start with the question: what is executing right now?

sys.dm_exec_requests has one row for each request executing in SQL Server. A request is an executing command or batch; a session is the logical login context that can exist while idle; a connection is the transport relationship carrying traffic. Those scopes differ. Joining them lets you distinguish “this login is connected” from “this request is currently waiting.” On SQL Server 2022 and later, seeing other sessions through many performance DMVs requires VIEW SERVER PERFORMANCE STATE. Granting sysadmin merely to let a monitoring login see DMVs destroys the least-privilege boundary.

USE master;GOIF DB_ID(N'ServiceHubObservabilityLab') IS NULLBEGIN    CREATE DATABASE ServiceHubObservabilityLab;END;GOALTER DATABASE ServiceHubObservabilityLab SET COMPATIBILITY_LEVEL = 170;ALTER DATABASE ServiceHubObservabilityLabSET QUERY_STORE = ON(    OPERATION_MODE = READ_WRITE,    QUERY_CAPTURE_MODE = AUTO,    WAIT_STATS_CAPTURE_MODE = ON);GOUSE ServiceHubObservabilityLab;GOIF SCHEMA_ID(N'lab22') IS NULL EXEC(N'CREATE SCHEMA lab22 AUTHORIZATION dbo;');GOIF OBJECT_ID(N'lab22.WorkOrder', N'U') IS NULLBEGIN    CREATE TABLE lab22.WorkOrder    (        work_order_id bigint NOT NULL CONSTRAINT PK_lab22_WorkOrder PRIMARY KEY,        customer_id   int NOT NULL,        status        varchar(16) NOT NULL,        priority      tinyint NOT NULL,        opened_at     datetime2(0) NOT NULL,        payload       char(200) NOT NULL CONSTRAINT DF_lab22_payload DEFAULT('x')    );    ;WITH n AS    (        SELECT TOP (5000)            ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS rn        FROM sys.all_objects AS a        CROSS JOIN sys.all_objects AS b    )    INSERT lab22.WorkOrder(work_order_id, customer_id, status, priority, opened_at)    SELECT rn,           1 + (rn % 200),           CASE WHEN rn % 7 = 0 THEN 'ESCALATED' WHEN rn % 3 = 0 THEN 'CLOSED' ELSE 'OPEN' END,           1 + (rn % 5),           DATEADD(minute, -rn, SYSUTCDATETIME())    FROM n;    CREATE INDEX IX_lab22_WorkOrder_StatusOpened        ON lab22.WorkOrder(status, opened_at)        INCLUDE(customer_id, priority);END;GO

Run the next observer query from a dedicated monitoring window. It avoids SELECT * because Microsoft can append columns to DMVs in later releases, and because a production collector should deliberately persist only fields whose meaning it understands.

SELECT    SYSDATETIMEOFFSET() AS observed_at,    r.session_id,    r.request_id,    r.status AS request_status,    r.command,    r.blocking_session_id,    r.wait_type,    r.wait_time,    r.wait_resource,    r.cpu_time,    r.total_elapsed_time,    s.login_name,    s.host_name,    s.program_name,    c.client_net_address,    at.transaction_begin_time,    txt.text AS batch_textFROM sys.dm_exec_requests AS rJOIN sys.dm_exec_sessions AS s  ON s.session_id = r.session_idLEFT JOIN sys.dm_exec_connections AS c  ON c.session_id = r.session_idLEFT JOIN sys.dm_tran_session_transactions AS stx  ON stx.session_id = r.session_idLEFT JOIN sys.dm_tran_active_transactions AS at  ON at.transaction_id = stx.transaction_idOUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS txtWHERE r.session_id <> @@SPIDORDER BY r.total_elapsed_time DESC;

A nonzero blocking_session_id identifies the immediate blocker reported for that request. It does not by itself tell you whether that blocker is the root of a longer chain, whether the blocker is actively executing, or whether an application has abandoned a transaction. An idle session can hold locks because the transaction belongs to the session, not to a currently executing request.

2. Reproduce a safe blocking chain and inspect the locks

Open three query windows. Window A starts a transaction and deliberately leaves it open. Window B requests the same row and waits. Window C is the observer. The table is disposable; the rollback strategy is simply ROLLBACK in Window A or dropping the lab database at the end of the chapter.

USE ServiceHubObservabilityLab;GOBEGIN TRAN;UPDATE lab22.WorkOrderSET status = 'HOLD'WHERE work_order_id = 1;SELECT @@SPID AS blocker_session_id, XACT_STATE() AS xact_state;-- Leave this transaction open until the observer has captured evidence.

USE ServiceHubObservabilityLab;GOSELECT @@SPID AS blocked_session_id;SELECT work_order_id, statusFROM lab22.WorkOrderWHERE work_order_id = 1;-- This SELECT can wait until Window A commits or rolls back.

SELECT    l.request_session_id,    l.resource_type,    l.resource_database_id,    l.resource_description,    l.request_mode,    l.request_status,    r.blocking_session_id,    r.wait_type,    r.wait_resourceFROM sys.dm_tran_locks AS lLEFT JOIN sys.dm_exec_requests AS r  ON r.session_id = l.request_session_idWHERE l.resource_database_id = DB_ID(N'ServiceHubObservabilityLab')ORDER BY l.request_session_id, l.resource_type, l.request_mode;

The exact lock resource description can differ with plan shape and engine internals, so the lesson does not ask you to memorize one resource string. The stable interpretation is relational: one session owns an incompatible lock while another request waits for a resource in the same transaction path. After capturing the evidence, return to Window A and run ROLLBACK;. Window B should complete and read the original value.

3. Plans and cached text answer a different question

The current request carries a sql_handle and usually a plan_handle. sys.dm_exec_sql_text retrieves the batch text; sys.dm_exec_query_plan retrieves the cached XML plan when one is available. A cached plan is not proof that the request spent most of its elapsed time inside a particular operator. Likewise, an actual execution plan requires executing the query and can perturb the workload. During an incident, begin with the evidence already available.

SELECT    r.session_id,    r.statement_start_offset,    r.statement_end_offset,    txt.text AS batch_text,    qp.query_planFROM sys.dm_exec_requests AS rOUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS txtOUTER APPLY sys.dm_exec_query_plan(r.plan_handle) AS qpWHERE r.database_id = DB_ID(N'ServiceHubObservabilityLab');

If the plan has been evicted or the request does not expose a usable plan handle, absence is not proof that no plan existed. Query Store, when enabled, is the persisted source for historical plan/runtime aggregates. Chapter 10 and Chapter 11 already taught plan interpretation and Query Store; this chapter uses those components as one layer in a broader incident timeline.

4. The dangerous shortcut: kill the visible blocker

A common wrong response is: “blocking_session_id is nonzero, therefore kill that SPID.” That treats a symptom as a diagnosis. The blocker might be a short, valid transaction; the head blocker might be elsewhere; rollback itself can be expensive; and the application may simply reproduce the same blocking pattern after reconnecting.

Another misleading approach is taking one DMV screenshot and treating it as history. A request row can disappear before an engineer opens SSMS. If the incident matters, persist timestamped snapshots, enable focused Extended Events before the next occurrence, and correlate Query Store and host metrics. Lesson 5 builds that timeline.

5. Production judgment: observe with least privilege and explicit retention

Monitoring queries themselves consume CPU, compile plans, traverse shared structures, and can expose sensitive SQL text. Collect only what is needed at a cadence that matches the diagnostic question. For SQL Server 2022+, prefer VIEW SERVER PERFORMANCE STATE or the corresponding fixed server role when it satisfies the collector instead of broad administrative membership. Treat query text, host names, login names and parameters as potentially sensitive telemetry.

Write down collector scope, sampling interval, retention, reset conditions, timezone, build, database compatibility level, AG replica role, and Query Store state alongside the data. A number without those dimensions is easy to misread later.

Authoritative references

• sys.dm_exec_requests (Transact-SQL)
• Dynamic management views and functions
• Server-level roles and performance-state reader role
• sys.dm_tran_locks
• sys.dm_exec_query_plan