View active requests and their SQL query

The application is responding slowly and we need to know what SQL Server is running right now. A list of connected sessions is not enough: a connection can exist without having an active request.

What we need to distinguish

A session is the client’s logical connection. A request is the work it is executing now, and a wait indicates the resource or condition temporarily stopping it.

We’ll gather the request, the session that sent it, and the associated SQL text. We will separate the elapsed time, observed consumption, and current wait so as not to call any request that appears in the list a “slow query.”

Query

/*
    Antes de ejecutar:
        Revisa los permisos y los límites antes de ejecutar la consulta.
        Confirma el alcance, la base activa y el costo posible de la consulta.
        Captura evidencia en varios momentos antes de atribuir una causa.

    Este script es de observación. No aplica correcciones ni modifica datos.
*/

SET NOCOUNT ON;
GO
-- Consulta
SELECT
    r.session_id AS IdSesion,
    r.request_id AS IdSolicitud,
    DB_NAME(r.database_id) AS BaseDatos,
    s.login_name AS InicioSesion,
    s.host_name AS EquipoCliente,
    s.program_name AS Aplicacion,
    r.status AS EstadoSolicitud,
    r.command AS Comando,
    r.blocking_session_id AS IdSesionBloqueadora,
    r.wait_type AS TipoEspera,
    r.wait_time AS EsperaMilisegundos,
    r.cpu_time AS CpuMilisegundos,
    r.total_elapsed_time AS DuracionMilisegundos,
    r.logical_reads AS LecturasLogicas,
    r.reads AS LecturasFisicas,
    r.writes AS Escrituras,
    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
    ) AS SentenciaActual,
    txt.text AS LoteCompleto
FROM sys.dm_exec_requests AS r
INNER JOIN sys.dm_exec_sessions AS s
    ON s.session_id = r.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS txt
WHERE s.is_user_process = 1
  AND r.session_id <> @@SPID
ORDER BY
    r.total_elapsed_time DESC,
    r.session_id;
GO

Parameters or permissions

The query excludes its own session using @@SPID. In SQL Server 2019 and earlier, viewing all requests typically requires VIEW SERVER STATE; VIEW SERVER PERFORMANCE STATE is required in SQL Server 2022 or later. Without those permissions, the output may be limited to the current session. Statement offsets are expressed in bytes. That is why they are divided by two when applied to Unicode text.

How to read the result

RequestState indicates whether the request is running, ready to get CPU, or suspended while waiting. DurationMilliseconds measures the time since the request arrived; It is not equivalent to CPU time. A high duration coupled with low CPU may point to waits, but you still need to check WaitType and the context. CurrentStatement attempts to isolate the statement within the batch. FullBatch helps you understand variables and previous steps. In parallel requests, some counters in the coordinator queue alone do not reflect all the work of the participating tasks.

What this does establish

It allows you to identify active user requests at the time of capture, the associated text and various initial consumption or waiting signals.

What this still does not establish

It does not retain history or demonstrate that the request is the primary cause of any slowness observed before or since. An isolated capture does not represent its usual behavior either.

Next reasonable check

If a request is blocked, identifies the blocking and blocked sessions. If it appears suspended due to another wait, checks the waits of its active tasks. To compare runs over time you need cumulative statistics or Query Store.

Search SQL Server