SP_WHO4

Muchas veces en la administración y/o optimización de bases de datos en MS SQLSERVER, usamos el siguiente script:

USE [master];
EXEC sp_who3

Pero dedibo a que hace muchas consultas para entregar el resultado en ocasiones donde la instancia está colapsada, este script de monitoreo recientemente lo adquirí para cuando tengo esas complicaciones y procedo a presentarselos

1. Consulta SP_WHO4

El script selecciona varias columnas de las vistas de administración dinámica (DMVs) para obtener información sobre las consultas en ejecución:

USE [master];
SELECT 
    r.[session_id],								-- ID de la sesión
    DB_NAME(r.[database_id]) AS DatabaseName,	-- Nombre de la base de datos    
    s.[login_name] AS LoginName,				-- Nombre del usuario que ejecuta la sesión
    s.[host_name] AS HostName,					-- Nombre del host desde el que se ejecuta
    s.[login_time] AS SessionStartTime,			-- Hora de inicio de la sesión
    r.[status],									-- Estado de la solicitud (RUNNING, SUSPENDED, etc.)
    SUBSTRING(qt.[text], r.[statement_start_offset] / 2,
        (CASE WHEN r.[statement_end_offset] = -1
              THEN LEN(CONVERT(NVARCHAR(MAX), qt.[text])) * 2
              ELSE r.[statement_end_offset]
        END - r.[statement_start_offset]
        ) / 2
    ) AS QueryText,								-- Parte de la consulta que está ejecutándose ahora
    convert(varchar(8), DATEADD(SECOND, (r.[cpu_time]/1000), 0), 108) AS CpuTime,	-- Tiempo de CPU utilizado (ms)
    convert(varchar(8), DATEADD(SECOND,(r.[total_elapsed_time]/1000), 0), 108) AS TotalElapsedTime,	-- Tiempo total de ejecución (ms)
    r.[last_wait_type] AS LastWaitType,			-- Último tipo de espera
    r.[wait_type] AS CurrentWaitType,			-- Tipo de espera actual
    r.[blocking_session_id] AS BlockingSessionID,	-- ID de la sesión que está bloqueando, si existe
    r.[reads] AS PhysicalReads,					-- Lecturas físicas realizadas
    r.[writes] AS PhysicalWrites,				-- Escrituras físicas realizadas
    r.[logical_reads] AS LogicalReads,			-- Lecturas lógicas realizadas (en caché)
    r.[scheduler_id],							-- ID del programador
    r.[plan_handle],							-- Identificador del plan de ejecución
    r.[sql_handle]								-- Identificador del lote/consulta
    

2. Unión de vistas de administración dinámica (DMVs)

El script utiliza las siguientes DMVs:

FROM sys.dm_exec_requests r
INNER JOIN sys.dm_exec_sessions s
    ON r.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS qt
    

• sys.dm_exec_requests: Proporciona información sobre las solicitudes (consultas) en ejecución.
• sys.dm_exec_sessions: Proporciona información sobre las sesiones activas.
• sys.dm_exec_sql_text: Devuelve el texto completo de la consulta asociada a un sql_handle.

3. Filtrado de resultados

El script filtra los resultados para excluir sesiones del sistema y consultas específicas:

WHERE r.session_id > 50									-- Excluir sesiones del sistema
AND SUBSTRING(qt.text, r.statement_start_offset / 2,
        (CASE WHEN r.statement_end_offset = -1
              THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
              ELSE r.statement_end_offset END - r.statement_start_offset) / 2
    ) NOT LIKE '%select r.session_id%'
AND SUBSTRING(qt.text, r.statement_start_offset / 2,
        (CASE WHEN r.statement_end_offset = -1
              THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
              ELSE r.statement_end_offset END - r.statement_start_offset) / 2
    ) NOT LIKE '%select * from OpenRowset(TrcData, @traceid, @records)%'
    

4. Ordenación de resultados

Los resultados se ordenan por tiempo total de ejecución y tiempo de CPU:

ORDER BY r.total_elapsed_time DESC, r.cpu_time DESC;
    

5. Ejemplo de salida

La salida del script podría verse así:

session_id	DatabaseName	LoginName	HostName	SessionStartTime	status	QueryText	CpuTime	TotalElapsedTime	LastWaitType	BlockingSessionID	PhysicalReads	LogicalReads
52	AdventureWorks	user1	ELT-DB12	2023-10-01 10:00:00	RUNNING	SELECT * FROM Orders	00:00:05	00:00:10	PAGEIOLATCH	NULL	100	500
53	master	user2	ELT-DB13	2023-10-01 10:01:00	SUSPENDED	UPDATE Customers SET...	00:00:02	00:00:15	LCK_M_X	52	50	200

6. Conclusión

Este script es una herramienta poderosa para monitorear y diagnosticar problemas de rendimiento en SQL Server. Proporciona información detallada sobre las consultas en ejecución, lo que permite identificar cuellos de botella, bloqueos y consultas costosas.