Cómo detectar consultas lentas en SQL Server

"El sistema está lento" es un reporte que no se puede corregir. Para optimizar, hay que pasar de esa frase a una lista concreta: estas cinco consultas consumen el 70 % del tiempo de la base de datos.

Lo interesante es que la consulta más lenta casi nunca es la más importante. Una consulta de 8 segundos que corre una vez al día pesa menos que una de 40 ms que corre 3.000 veces por minuto.

Duración contra costo total

Antes de buscar, hay que decidir qué buscar:

• Por duración individual: encuentra las consultas que hacen esperar a un usuario concreto.
• Por costo total (duración o CPU × número de ejecuciones): encuentra las que más cargan el servidor.

Las dos listas suelen ser distintas. La segunda es la que más mejora el sistema en general.

Opción 1: Query Store (la primera que deberías activar)

Query Store guarda el historial de consultas, sus planes y sus métricas dentro de la propia base de datos. Sobrevive a reinicios y permite comparar periodos. Está activo por defecto en bases nuevas desde SQL Server 2022 y en Azure SQL Database. En versiones anteriores hay que activarlo:

ALTER DATABASE [MiBase] SET QUERY_STORE = ON
    (OPERATION_MODE = READ_WRITE, QUERY_CAPTURE_MODE = AUTO);

Las consultas que más CPU consumieron en las últimas 24 horas:

SELECT TOP (20)
    q.query_id,
    qt.query_sql_text,
    SUM(rs.count_executions) AS executions,
    SUM(rs.avg_cpu_time * rs.count_executions) / 1000 AS total_cpu_ms,
    AVG(rs.avg_duration) / 1000 AS avg_duration_ms
FROM sys.query_store_query AS q
JOIN sys.query_store_query_text AS qt ON qt.query_text_id = q.query_text_id
JOIN sys.query_store_plan AS p ON p.query_id = q.query_id
JOIN sys.query_store_runtime_stats AS rs ON rs.plan_id = p.plan_id
JOIN sys.query_store_runtime_stats_interval AS i ON i.runtime_stats_interval_id = rs.runtime_stats_interval_id
WHERE i.start_time >= DATEADD(HOUR, -24, SYSUTCDATETIME())
GROUP BY q.query_id, qt.query_sql_text
ORDER BY total_cpu_ms DESC;

En SSMS, los informes Top Resource Consuming Queries y Regressed Queries muestran lo mismo con gráficos. El segundo es especialmente útil: detecta consultas que empeoraron porque cambió su plan, y permite forzar el plan anterior mientras se investiga la causa.

Opción 2: DMVs (lo que está en caché ahora)

Si no tienes Query Store, las vistas de administración dinámica muestran las estadísticas de los planes que están en caché:

SELECT TOP (20)
    qs.execution_count,
    qs.total_worker_time / 1000 AS total_cpu_ms,
    qs.total_elapsed_time / qs.execution_count / 1000 AS avg_duration_ms,
    qs.total_logical_reads / qs.execution_count AS avg_logical_reads,
    SUBSTRING(st.text, (qs.statement_start_offset / 2) + 1,
        ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(st.text)
          ELSE qs.statement_end_offset END - qs.statement_start_offset) / 2) + 1) AS statement_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY qs.total_worker_time DESC;

La limitación: solo ve lo que está en caché. Si el servidor se reinició o un plan se recompiló, esa historia se perdió. Sirve para una foto del momento, no para comparar semanas.

Opción 3: Extended Events (para capturar casos concretos)

Cuando necesitas ver cada ejecución que supere un umbral, con el texto y los parámetros, Extended Events es la herramienta. Reemplaza a SQL Profiler con mucho menos impacto:

CREATE EVENT SESSION [ConsultasLentas] ON SERVER
ADD EVENT sqlserver.rpc_completed (
    ACTION (sqlserver.sql_text, sqlserver.client_app_name, sqlserver.database_name)
    WHERE duration > 1000000), -- microsegundos: más de 1 segundo
ADD EVENT sqlserver.sql_batch_completed (
    ACTION (sqlserver.sql_text, sqlserver.client_app_name, sqlserver.database_name)
    WHERE duration > 1000000)
ADD TARGET package0.event_file (SET filename = N'ConsultasLentas.xel', max_file_size = 50)
WITH (STARTUP_STATE = OFF);

ALTER EVENT SESSION [ConsultasLentas] ON SERVER STATE = START;

Las consultas que envía una aplicación .NET con parámetros llegan como rpc_completed, no como sql_batch_completed. Si solo capturas el segundo, no verás casi nada de lo que ejecuta EF Core o Dapper.

Desde la aplicación: une el SQL con el endpoint

La base de datos sabe qué consulta fue lenta, pero no qué endpoint la ejecutó. Dos detalles ayudan a cerrar esa brecha:

• Application Name en la cadena de conexión, distinto por aplicación (App=Api / App=Worker). Aparece en client_app_name.
• Etiquetas en la consulta. En EF Core, TagWith("Listado de pedidos") agrega un comentario al SQL que luego encuentras en Query Store.

Con un APM como Application Insights u OpenTelemetry, cada petición muestra sus dependencias SQL con su duración, y el camino entre "este endpoint es lento" y "esta consulta es la culpable" se vuelve inmediato.

El trade-off

Cada herramienta de captura consume algo: Query Store usa espacio y algo de CPU, Extended Events escribe archivos y un APM agrega latencia mínima por petición. En la mayoría de sistemas ese costo es muy pequeño frente a lo que se gana, pero conviene configurar límites (tamaño máximo de Query Store, umbrales en Extended Events) en lugar de capturar todo.

Cuándo no buscar más

Si encontraste que cinco consultas explican la mayor parte de la carga, detente ahí. Optimiza esas, mide de nuevo y repite. Buscar la consulta número 40 rara vez cambia algo que un usuario note.

Conclusión

Query Store para la historia, DMVs para la foto de ahora y Extended Events para casos concretos. Con eso, "el sistema está lento" se convierte en una lista corta de consultas. Y para cada una, el siguiente paso es leer su plan de ejecución.