Parameter sniffing en SQL Server: diagnostica antes de corregir

Un procedimiento almacenado tarda 80 milisegundos para un cliente y 40 segundos para otro. El código no cambió, el servidor no está saturado y, a veces, una nueva ejecución parece eliminar el problema. Es una señal clásica de un plan sensible a parámetros, conocido como parameter sniffing.

El parameter sniffing no es un defecto por sí mismo. Cuando SQL Server compila una consulta parametrizada, utiliza los valores actuales para estimar filas y elegir un plan. Reutilizar ese plan ahorra compilaciones y normalmente es beneficioso. El problema surge cuando un único plan debe atender distribuciones de datos muy distintas.

Por qué puede fallar un plan

Supongamos que la mayoría de los clientes tiene menos de 100 pedidos, pero un gran cliente tiene 8 millones. Un plan compilado para un cliente pequeño puede elegir búsquedas por índice y bucles anidados. Ese plan puede ser extremadamente lento para el cliente grande. En cambio, un plan compilado para el cliente grande puede usar escaneos y hashes que desperdician recursos para los demás.

Los datos sesgados, filtros opcionales, estadísticas antiguas y columnas correlacionadas aumentan el riesgo.

Reproduce el comportamiento de forma segura

Parte de un procedimiento representativo:

CREATE OR ALTER PROCEDURE dbo.GetOrdersByCustomer
    @CustomerId int
AS
BEGIN
    SET NOCOUNT ON;

    SELECT OrderId, OrderDate, TotalAmount
    FROM dbo.Orders
    WHERE CustomerId = @CustomerId
    ORDER BY OrderDate DESC;
END;

Prueba un valor selectivo y otro poco selectivo fuera de producción. Captura el plan de ejecución real y las estadísticas de ambas ejecuciones. Si el rendimiento depende del valor que compiló el plan en caché, existe evidencia sólida de sensibilidad a parámetros.

No vacíes toda la caché de planes en producción. Provoca un pico de compilaciones y afecta cargas no relacionadas.

Flujo de diagnóstico práctico

1. Usa Query Store para localizar grandes variaciones de duración, CPU o lecturas lógicas.
2. Compara las filas reales y estimadas en el primer join o lookup importante.
3. Registra los parámetros usados durante compilación y ejecución.
4. Revisa el histograma y la fecha de actualización de las estadísticas relevantes.
5. Descarta conversiones implícitas, predicados no sargables e índices ausentes.
6. Confirma que las ejecuciones rápida y lenta comparten consulta e identidad de plan.

La pregunta clave no es "¿El plan es malo?", sino "¿Es bueno para una forma importante de los datos y malo para otra?"

Elige la solución fiable más pequeña

Empieza por lo básico. Actualiza estadísticas inexactas y crea un índice solo si el patrón de acceso lo justifica. Si la consulta tiene formas realmente diferentes, separarlas suele ser más claro que imponer un plan de compromiso.

Desde SQL Server 2022, Parameter Sensitive Plan Optimization puede mantener varias variantes de plan para determinados predicados de igualdad. Verifica el nivel de compatibilidad y la forma de la consulta, y confirma las variantes en Query Store.

Un hint de Query Store o un plan forzado puede estabilizar una emergencia, pero necesita seguimiento porque los datos cambian.

Usa OPTION (RECOMPILE) cuando compilar sea barato y cada ejecución necesite un plan adaptado. Aplícalo a la instrucción más pequeña posible. Usa OPTIMIZE FOR solo con un valor estable y representativo o cuando busques deliberadamente un plan promedio. El SQL dinámico con sp_executesql resulta útil para filtros opcionales porque genera planes para formas distintas sin perder la parametrización.

Evita errores comunes

• No desactives el parameter sniffing globalmente por una sola consulta.
• No añadas cada índice sugerido sin medir el coste de escritura y almacenamiento.
• No fuerces un plan sin responsable, supervisión y condición de retirada.
• No atribuyas toda ralentización intermitente al parameter sniffing.

Checklist de producción

Captura una línea base, conserva el plan original, prueba grupos de parámetros representativos, mide CPU y lecturas lógicas, despliega el cambio más limitado y vigila Query Store. Define la reversión antes del despliegue.

Conviene tratar el parameter sniffing como un problema de distribución de datos, no como un fallo misterioso de caché. Demuestra las formas de datos que compiten y elige una solución que se ajuste a ellas. Para una segunda opinión, usa el formulario de preguntas inferior e incluye el procedimiento, parámetros representativos y un plan de ejecución anonimizado.