SQL Optimización del plan de ejecución del procedimiento: el parámetro Sniffing y su impacto

El rendimiento de los procedimientos almacenados de SQL puede verse significativamente afectado por un fenómeno conocido como parámetro de parámetro. Esto ocurre cuando el motor de la base de datos compila el plan de ejecución de un procedimiento almacenado en función de los valores de los parámetros de entrada proporcionados en el momento de la compilación.

Considere el siguiente procedimiento almacenado:

CREATE PROCEDURE MyProc
    @MyDate DATETIME = NULL
AS
    IF @MyDate IS NULL SET @MyDate = CURRENT_TIMESTAMP
    -- Do Something using @MyDate

En este escenario, si @mydate se pasa como nulo durante la compilación inicial, la base de datos optimiza el plan de ejecución para este valor. Sin embargo, las llamadas posteriores al procedimiento con otros valores de entrada pueden dar lugar a un bajo rendimiento, incluso si esos valores también son nulos.

Impact of Parameter Sniffing

Parameter Sniffing puede tener varias consecuencias negativas:

[&]
• TIEMPLES DE EJECUCIÓN DE ESCRISICIÓN El procedimiento puede variar drásticamente dependiendo de los valores de entrada pasados, lo que lleva a tiempos de ejecución impredecibles.
planes de ejecución pobres:
• El motor puede generar planes de ejecución ineficientes que no son óptimos para los datos reales que se procesan, lo que resulta en la ejecución de consultas lentas. Incorrecto
En el caso mencionado, el plan de ejecución generado para @mydate fue pobre incluso cuando el valor utilizado era nulo. Este comportamiento es inusual y sugiere un problema con los parámetros que sniffing.

Solución: deshabilitar el parámetro Sniffing

una solución es deshabilitar el parámetro que se rastrea utilizando una variable de parámetro como sigue:

creación de procedimiento myPROC @Mydate datetime = nulo COMO Declarar @mydate_copy dateTime Set @mydate_copy = @mydate Si @mydate_copy es nulo set @mydate_copy = current_timestamp - Haga algo usando @mydate_copy

CREATE PROCEDURE MyProc
    @MyDate DATETIME = NULL
AS
    DECLARE @MyDate_Copy DATETIME
    SET @MyDate_Copy = @MyDate
    IF @MyDate_Copy IS NULL SET @MyDate_Copy = CURRENT_TIMESTAMP
    -- Do Something using @MyDate_Copy

consideraciones adicionales

para mejorar aún más el rendimiento cuando se usa procedimientos almacenados con los parámetros de entrada, se recomienda:

. eficientemente.

• ]