Capturar consultas lentas con Extended Events en SQL Server

Una consulta lenta intermitente es difícil de investigar cuando ya terminó. Un plan de otra ejecución puede no explicar si el tiempo se consumió en CPU, lecturas o esperas. Una sesión de Extended Events enfocada conserva evidencia útil sin registrar todos los detalles de todas las instrucciones.

La pregunta aquí es concreta: ¿qué solicitudes terminadas en una base duran al menos un segundo? Limita la captura y encuentra candidatos para profundizar. El ejemplo corresponde a una instancia SQL Server convencional; sesiones de nube con ámbito de base y otros destinos requieren otra configuración.

Definir la captura antes de iniciarla

Comprueba los metadatos en la instancia real en vez de adivinar unidades.

SELECT object_name, name, type_name, description
FROM sys.dm_xe_object_columns
WHERE object_name IN (N'rpc_completed', N'sql_batch_completed')
  AND name IN (N'duration', N'cpu_time');

En los eventos de finalización RPC y batch usados aquí, duration está en microsegundos: 1.000.000 equivale a un segundo. No supongas que todos los campos temporales usan esa unidad. Consérvala al exportar resultados.

La sesión filtra la base con ID 5. Cámbialo por DB_ID de la base deseada y sustituye la ruta Windows por un directorio existente donde escriba la cuenta de servicio de SQL Server. Elige un nombre de sesión que no exista.

-- SQL Server instance example: replace database_id 5 and the file path.
CREATE EVENT SESSION AxialSlowRequestsDemo ON SERVER
ADD EVENT sqlserver.rpc_completed (
    ACTION(sqlserver.client_app_name, sqlserver.database_name,
           sqlserver.session_id, sqlserver.sql_text)
    WHERE ([duration] >= (1000000) AND [sqlserver].[database_id] = (5))
),
ADD EVENT sqlserver.sql_batch_completed (
    ACTION(sqlserver.client_app_name, sqlserver.database_name,
           sqlserver.session_id, sqlserver.sql_text)
    WHERE ([duration] >= (1000000) AND [sqlserver].[database_id] = (5))
)
ADD TARGET package0.event_file (
    SET filename = N'D:\XEvents\AxialSlowRequests.xel',
        max_file_size = (20), max_rollover_files = (4)
)
WITH (MAX_MEMORY = 4096 KB,
      EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
      MAX_DISPATCH_LATENCY = 5 SECONDS,
      STARTUP_STATE = OFF);
ALTER EVENT SESSION AxialSlowRequestsDemo ON SERVER STATE = START;

Los RPC cubren llamadas como procedimientos enviados por un controlador; los batches cubren bloques SQL enviados. Capturar ambos evita una omisión según cómo trabaje la aplicación. No convierte sus medidas en intercambiables ni permite sumar duraciones sin revisar su alcance.

El destino limita tamaño y rotación de archivos. Los antiguos pueden desaparecer al rotar, por lo que es una ventana diagnóstica y no un archivo permanente. STARTUP_STATE OFF impide que la investigación temporal se reactive automáticamente después de reiniciar.

ALLOW_SINGLE_EVENT_LOSS acepta la posibilidad de perder eventos. No describas la captura como auditoría exacta. Supervisa salud y pérdidas cuando la integridad de la observación sea importante.

Leer evidencia para elegir la siguiente prueba

Inspecciona los archivos con la misma ruta.

SELECT object_name, CAST(event_data AS xml) AS EventXml
FROM sys.fn_xe_file_target_read_file(
    N'D:\XEvents\AxialSlowRequests*.xel', NULL, NULL, NULL
);

El XML contiene fecha, campos propios del evento y acciones elegidas. Interpreta los horarios coherentemente, normalmente UTC, al compararlos con registros de aplicación. Lee duración, CPU, lecturas lógicas y texto según el esquema de cada evento.

Una duración alta con poco CPU invita a investigar esperas, bloqueos, almacenamiento u otros retrasos, pero no identifica una causa por sí sola. Muchas lecturas pueden señalar acceso excesivo; CPU alto puede justificar revisar planes y expresiones. Relaciona la solicitud concreta con el plan y parámetros que realmente produjo.

Los eventos completados no explican una consulta que sigue ejecutándose indefinidamente. Durante un incidente activo, revisa solicitudes y esperas actuales por separado. Red y renderizado del cliente tampoco forman parte de la duración SQL capturada.

El texto puede contener literales y datos sensibles. Limita acceso a archivos, acciones recogidas y retención. Revisa su contenido antes de adjuntar archivos sin procesar a conversaciones de soporte amplias.

Detener y conservar la conclusión

Después de reproducir o agotar el tiempo, detén y elimina la sesión temporal.

ALTER EVENT SESSION AxialSlowRequestsDemo ON SERVER STATE = STOP;
DROP EVENT SESSION AxialSlowRequestsDemo ON SERVER;

Borrar la definición no elimina los archivos. Conserva la evidencia relevante y aplica la retención acordada. Una investigación posterior no debería leer archivos antiguos ajenos por usar un comodín demasiado amplio.

No empieces capturando instrucciones de alto volumen y planes completos de toda la instancia. Amplía solo para responder una duda concreta y mide el impacto. Un filtro aparentemente estrecho puede coincidir con miles de solicitudes durante el incidente.

Registra base, periodo, umbral, tipos, pérdidas e hipótesis. Después aplica un cambio focalizado o toma la siguiente medición necesaria. El valor de Extended Events es vincular una solicitud lenta real con una explicación comprobable, no simplemente crear otro archivo de traza.

Referencias técnicas: Microsoft Learn: Extended Events quick start · Microsoft Learn: CREATE EVENT SESSION · Microsoft Learn: Read event files.