¿Qué está el Parameter Sniffing ?

¿Qué está el Parameter Sniffing ?
 
No tenga miedo del "parameter sniffing". Este es un comportamiento normal del motor.

Cuando un procedimiento almacenado se compila o se vuelve a compilar, los valores de los parámetros se "olió" y se utiliza para la estimación de cardinalidad. El plan se optimiza con estos valores de los parámetros como los valores littérals en la consulta. Considere el siguiente procedimiento almacenado:

Supposons que la table posséde environ 100.000 lignes, et un index de colonne unique non ordonné en clusters sur la colonne Keyword.

Digamos que usted llame por primera vez como un parámetro @Keyword LIKE '%XBOX'. Supongamos que el número de filas en la tabla con la XBOX valor es muy pequeño - algunas docenas de líneas. El optimizador puede optar por utilizar un plan de consulta que utiliza el índice en la columna Keyword para evaluar el LIKE, a continuación, un bucle para recuperar las otras columnas de la fila.

Este index seek + bookmark lookup se almacenan en caché y reutilizar para posteriores ejecuciones del procedimiento almacenado.

Los problemas de rendimiento causada por Parameter Sniffing.

Sin embargo, en algún momento en el futuro, el motor debe compilar / recompilar un nuevo plan para el procedimiento almacenado (el plan puede ser demasiado viejo o hacer estadísticas al día expulsa el plan para la tabla de productos, etc.). Por desgracia , la ejecución del procedimiento, que compiló el nuevo plan tenía un parámetro @Keyword LIKE'KINECT. Supongamos que el filtro 'KINECT%" devuelve el 10% de las filas de la tabla. Cuando se compila el procedimiento con este parámetro, SQL puede seleccionar un plan de consulta que utiliza un escaneo completo de tabla. Este plan sería ideal para el parámetro 'KINECT% ", pero sería un desastre para otros valores con más selectividad.

Desafortunadamente, después de recopilación, el análisis de la tabla también podrían época en caché y reutilizar. El rendimiento de carreras posteriores con otros valores se degradaría.
 
El parameter sniffing permite SQL para elaborar un plan que sea apropiado para el tipo de parámetro que se pasa al procedimiento almacenado. En general, esta característica permite ahorrar la fase de compilación durante la ejecución del procedimiento almacenado, pero este mecanismo requiere de un requisito específico para todas las obras que se espera es que los valores de los parámetros utilizados para la compilación "típico". Como se ilustra en este ejemplo hipotético, un procedimiento o una consulta con parámetros, lamentablemente a veces puede ser ejecutado con un parámetro atípica (sesgo de los datos están a menudo involucrados en estos casos).

El descubrimiento de parámetros puede afectar a todo tipo de consultas, pero las consultas utilizando  LIKE son particularmente propensos a este problema. Los problemas de rendimiento causadas por el descubrimiento de parámetros se considera generalmente By Design.  

Plan de acción:

Desde SQL Server 2005, tenemos una nueva característica que permite la recopilación del statement individuales en lugar de elaborar el plan de ejecución para cualquier procedimiento almacenado.. A continuación encontrará algunas soluciones si se ven afectados por un problema de rendimiento.

       
1. Utilizar una variable para simular el motor. A medida que el @Keyworddummy variable no es parametrizable, el motor tendrá que volver a compilar cada vez que se ejecute el statment, el siguiente ejemplo:

create procedure dbo.SearchProducts
    @Keyword varchar(100)
As
Declare @Keyworddummy as varchar(100)
Set @Keyworddummy = @Keyword
select * from Products where Keyword like @Keyworddummy

2. Para evitar esto y otras situaciones similares, puede utilizar la siguiente consulta:

OPTIMIZE FOR
RECOMPILE

3. Desactivar la actualización de las estadísticas de forma automática.

Michel Degremont | Premier Field Engineer - SQL Server Core Engineer |