Parameter Sniffing en SQL Server: un SELECT compilado para el dominio equivocado bloqueó 200.000 INSERTs
Nota: Este artículo está basado en un incidente real de producción. Los datos de rendimiento son reales, pero los nombres de servidores, bases de datos, tablas y procedimientos han sido modificados para proteger la confidencialidad.
El reporte
El equipo de aplicaciones reporta un pico de latencia de ~5 minutos en los INSERTs sobre la tabla eventos de su plataforma de autenticación. La tabla recibe 200.000-500.000 INSERTs por hora y normalmente cada INSERT tarda microsegundos.
Paso 1: localizar la anomalía con Query Store
| Intervalo | Ejecuciones | avg_duration | max_duration | avg_reads |
|---|---|---|---|---|
| 07:00-08:00 | 371.308 | 0s | 0,89s | 11,76 |
| 08:00-09:00 | 512.237 | 0s | 1,08s | 11,66 |
| 12:00-13:00 | 203.937 | 0,22s | 299,25s | 11,52 |
| 13:00-14:00 | 186.696 | 0s | 2,58s | 11,58 |
El max_duration de 299 segundos (~5 minutos) confirma el pico, y el avg_duration subió a 0,22s indicando que las 203K ejecuciones se vieron afectadas.
Paso 2: confirmar bloqueo por locks
Query Store Wait Stats:
| Intervalo | Lock avg_wait | Lock max_wait | Lock total_wait |
|---|---|---|---|
| 11:00-12:00 | 1,86ms | 5.366ms | 496.142ms |
| 12:00-13:00 | 225ms | 299.245ms | 44.678.719ms |
| 13:00-14:00 | 1,58ms | 2.563ms | 282.779ms |
El max_wait por Lock coincide exactamente con el max_duration del INSERT. El problema fue de bloqueos.
Paso 3: identificar al bloqueador
Un SELECT sobre la misma tabla duró 5 minutos con 104 millones de lecturas y fue cancelado (Aborted). Normalmente tarda menos de 1 segundo.
SELECT id, client_id, details, ip_address, domain_id,
session_id, event_time, type, user_id
FROM eventos
WHERE domain_id = @P0 AND user_id = @P1
ORDER BY event_time DESC
OFFSET @P2 ROWS FETCH FIRST @P3 ROWS ONLYPaso 4: Parameter Sniffing
Query Store almacenaba dos planes:
| Plan usado el día del incidente | Plan usado normalmente | |
|---|---|---|
| @P0 (domain_id) | 'produccion' (millones de filas) | 'demo' (miles de filas) |
| Filas estimadas | 8.482.460 | 85.583 |
El plan se compiló cuando un usuario del dominio 'demo' ejecutó la query. Con pocas filas, era eficiente. Cuando se reutilizó para 'produccion' (millones de filas), el Key Lookup se convirtió en un escaneo masivo.
Estadísticas obsoletas
La estadística de user_id acumulaba 9,1 millones de modificaciones pendientes sobre 12,6M filas:
| Estadística | Columna | Sample % | Modificaciones pendientes |
|---|---|---|---|
| _WA_Sys_user_id | user_id | 0,92% | 9.153.671 |
| PK_eventos | id | 100% | 9.156.393 |
Un sample del 0,92% con 9M de modificaciones pendientes es una receta para estimaciones incorrectas.
La solución
1. Índice de cobertura (impacto 98,9%)
CREATE INDEX IX_eventos_domain_user
ON dbo.eventos (domain_id, user_id)
INCLUDE (client_id, details, ip_address,
session_id, event_time, type);2. Actualizar estadísticas
UPDATE STATISTICS dbo.eventos WITH FULLSCAN;Lecciones aprendidas
- Parameter Sniffing puede convertir un SELECT rápido en un bloqueador de toda la tabla. Un plan compilado para un dominio pequeño se reutilizó para millones de filas.
- Los SELECTs pueden bloquear INSERTs: bajo READ COMMITTED, un SELECT largo bloquea los INSERTs en las mismas páginas.
- 9 millones de modificaciones pendientes es una bomba de relojería. Configura auto-update statistics y FULLSCAN periódico en tablas con alta rotación.
- Mira siempre max_duration en Query Store: los picos de latencia pasan desapercibidos en los promedios.
- En aplicaciones multi-tenant, el parameter sniffing es especialmente peligroso: planes compilados para un tenant pequeño se reutilizan para tenants grandes.