M365Con 2027 – conferencia online gratuita · 11–22 Jan 2027 · 120 sessions m365con.net

Parameter sniffing

Concepto

Comportamiento de los motores de tipo SQL Server que compilan un plan para los valores de parámetro vistos en el momento de la compilación y reutilizan el plan en caché, lo que puede dar un rendimiento deficiente con valores de parámetro muy distintos; también se conoce como sensibilidad de parámetros.

✦ CONCEPTOParameter sniffingM365.FM
Gráfico de título: Parameter sniffing (Concepto)

Descripción

La detección de parámetros (parameter sniffing) es un comportamiento de los motores de bases de datos de tipo SQL Server en el que un plan de consulta se compila para los valores de parámetro vistos en el momento de la compilación y, después, el plan en caché se reutiliza. También se conoce como sensibilidad de parámetros. El comportamiento no es un defecto en sí mismo: el motor inspecciona los valores de parámetro actuales al compilar o recompilar una consulta y los pasa al optimizador de consultas, que puede entonces producir un plan de ejecución más eficiente para esos valores. La dificultad aparece cuando el plan en caché se utiliza más tarde con valores de parámetro que parecen muy distintos de los originales, porque un plan adecuado para un valor puede rendir mal con otro.

La guía de arquitectura de procesamiento de consultas de Microsoft para SQL Server describe la detección de parámetros en una sección sobre sensibilidad de parámetros. Según esa guía, el proceso se aplica a los procedimientos almacenados, a las consultas ejecutadas mediante sp_executesql y a las consultas preparadas. Interactúa directamente con la reutilización de planes, ya que los planes compilados se almacenan en la caché de planes y pueden ser reutilizados por ejecuciones posteriores. El primer conjunto de valores que desencadena una compilación determina, por tanto, el plan que reciben las llamadas siguientes, por lo que los resultados pueden variar según la llamada que haya compilado el plan.

La guía menciona un remedio: la sugerencia de consulta OPTION (RECOMPILE). Con esta sugerencia, el optimizador trata los parámetros y las variables locales como constantes en tiempo de compilación, que pueden sustituirse por literales. Los valores utilizados son los que están vigentes justo antes de ejecutar la instrucción, que no son necesariamente los valores originalmente pasados. Esto puede ayudar cuando los datos de entrada son muy variables o están sesgados. La guía también advierte de que la sugerencia recompila la consulta en cada ejecución, lo que puede resultar costoso, y aconseja medir el efecto sobre toda la carga de trabajo antes de adoptarla. Además, remite a un artículo aparte sobre la optimización Parameter Sensitive Plan para ampliar la información.

Un detalle relacionado de la misma documentación se refiere a las variables locales. Sus valores no se detectan en tiempo de compilación. En su lugar, el optimizador estima a partir de la información de densidad o, cuando no hay estadísticas disponibles, utiliza porcentajes fijos de estimación para las comparaciones de igualdad y de rango. Esta distinción es importante al diagnosticar una consulta lenta, ya que un parámetro y una variable local pueden dar lugar a estimaciones distintas y, por tanto, a planes diferentes.

En la práctica, la detección de parámetros se manifiesta como consultas que se ejecutan rápido para unas entradas y lento para otras, o como un rendimiento que cambia después de que un plan sea expulsado de la caché y recompilado. El episodio de M365.FM "Fix Bad Data Upstream with T-SQL Contracts" enumera la detección de parámetros entre los fallos habituales en entornos empresariales, describiéndola como algo que puede hacerse pasar por aleatoriedad. El episodio también plantea la forma de los planes como una cuestión de gobernanza, afirmando que si cambia la forma de los planes, cambian con ella el gasto y la fiabilidad, de modo que el comportamiento del plan se traduce en estabilidad de costes y riesgo operativo.

El episodio "Design Reliable and Resilient Azure Architectures" sitúa la detección de parámetros en un flujo de trabajo de resolución de problemas para Azure SQL Database. Sugiere habilitar Intelligent Insights y Query Performance Insight, capturar las consultas de larga duración, revisar los planes de ejecución e identificar los índices que faltan o la detección de parámetros. Menciona además las recomendaciones de ajuste automático de Azure SQL y las sugerencias de índices a través de Azure Advisor, junto con la optimización de consultas, la indexación adecuada y la posibilidad de escalar las DTU o vCores o de pasar a Hyperscale.

En conjunto, las fuentes tratan la detección de parámetros como un compromiso del almacenamiento en caché de planes: reutilizar un plan compilado ahorra trabajo de compilación, mientras que un plan ajustado a un valor de parámetro puede no ser adecuado para otros. Las decisiones habituales en torno a ella implican examinar los planes de ejecución, comprobar si la distribución de los datos está sesgada y sopesar el coste de compilación de opciones como OPTION (RECOMPILE) frente al beneficio de planes más adecuados.

Resumen generado con IA a partir de los episodios de M365.FM y los datos de esta página. Puedes mejorarlo con el botón de edición de abajo.

TipoConcepto

Preguntas frecuentes

Las preguntas marcadas con IA se escribieron con IA a partir de los datos de esta página.

¿Qué es la detección de parámetros? (IA)

Es el comportamiento de los motores de tipo SQL Server que compilan un plan de consulta para los valores de parámetro vistos en el momento de la compilación y reutilizan el plan en caché. Esto puede dar un rendimiento deficiente cuando los valores de parámetro posteriores difieren mucho. También se conoce como sensibilidad de parámetros.

¿Cómo puede afectar OPTION (RECOMPILE) a los problemas de detección de parámetros? (IA)

Según la guía de procesamiento de consultas de Microsoft, la sugerencia hace que el optimizador trate los parámetros y las variables locales como constantes en tiempo de compilación que pueden sustituirse por literales, lo que puede ayudar con datos muy variables o sesgados. La consulta se recompila en cada ejecución, lo que puede resultar costoso, por lo que conviene medir primero el efecto sobre toda la carga de trabajo.

¿Se detectan las variables locales de la misma manera que los parámetros? (IA)

No. La documentación indica que los valores de las variables locales no se detectan en tiempo de compilación. El optimizador estima, en cambio, a partir de la información de densidad o, sin estadísticas, utiliza porcentajes fijos de estimación para las comparaciones de igualdad y de rango.

Glosario

Community wiki

Short info above, community knowledge below: add practical tips, examples or corrections. New contributions are reviewed first; trusted members publish directly.

Expertos y empresas para este tema

Nadie ha indicado aún este tema. Añádelo a tu perfil.

Mantenido por Mirko Peters (Microsoft MVP) · Última actualización: