Grimpi IT Blog

enero 23, 2009

Parameter Sniffing

Filed under: Engine, SQL Server — Etiquetas: , , — grimpi @ 1:26 am

Parameter Sniffing es una técnica usada por los motores de base de datos (en este caso SQL Server) para optimizar el plan de ejecución de un store procedure dependiendo de los valores de los parámetros del mismo. En general, es una buena técnica que ayuda a mejorar la performance de la ejecución del store procedure, cuando en la mayoría de los escenarios donde se ejecuta, se trabaja con el mismo conjunto de datos.

Sin embargo, existen al menos 2 situaciones, donde se pueden presentar más inconvenientes que beneficios.

  • Parameter Sniffing es óptimo cuando en general, la mayoría de los valores de los parámetros tienen las mismas características (en realidad, el resultado de los valores de filtro en la tabla tienen la densidades similares). Si los valores que se le envían al Store Procedure, varían mucho en tamaño (cantidad de registros), el motor podría estar ejecutando un plan de ejecución que en la mayoría de los casos, no es el adecuado para el valor.
  • Otra razón en la cual puede haber problemas de performance, es cuando la distribución de datos en la tabla no es uniforme, por lo tanto, el plan de ejecución óptimo es muy sensible a ligeras variaciones en el filtrado de datos.


Muchas veces cuando pasa que una query se ejecuta rápido, pero la misma dentro de un store procedure no, es por problemas relacionados con Parameter Sniffing.


Veamos el siguiente ejemplo, para observar el problema que sucede en algunos casos relacionado con Parameters Sniffing.

1) Creo primero el siguiente Store Procedure:

CREATE PROCEDURE dbo.GetReservaCliente @Param2int
AS
BEGIN

SELECT * FROM Reserva R WHERE R.ClienteId = @Param2

END

Hay que recordar que el plan de ejecución de un Store Procedure no se genera cuando es creado el Store, sino cuando se ejecuta por primera vez. Esto significa que la segunda vez que se llama el procedimiento, va a usar el plan de ejecución que tiene cacheado.

2) Vemos la distribución de datos en la tabla Reserva por cada ClienteId:

SELECT ClienteId, Count(*) AS Cantidad FROM Reserva
GROUP BY ClienteId
ORDER BY Count(*)

El mínimo es el ClienteId 63, que tiene 1 solo registro.
El máximo es el ClienteId 1065, que tiene 28656 registros.

3) Buscamos ahora todos los registros de la tabla Reserva cuyo ClienteId sea 1065 usando el Store Procedure previamente creado.

EXEC GetReservaCliente 1065

Veamos el plan de ejecución que el SQL Server armó para la consulta:

2 cosas nos interesan acá: El plan de ejecución que armó y el “Estimated Number of Rows” (que está marcado en rojo). Como podemos ver, este valor es de 28656, exactamente la cantidad de registros existentes en la tabla para el cliente consultado. De donde sacó esta información el motor? En función de las estadísticas internas que almacena.
Ahora ejecutemos el mismo Store Procedure, pero buscando el ClienteId 63, que como vimos antes, tiene un solo registro.

EXEC GetReservaCliente 63

Veamos el plan de ejecución que el SQL Server armó para la consulta:

El valor de “Estimated Number of Rows” es de 28656, exactamente el mismo valor que la consulta anterior, a pesar de que la cantidad de registros devueltos es 1. Esto significa que el motor esta reutilizando el plan de ejecución anterior, que esta optimizado para devolver 28656 registros y por lo tanto no hay certeza que este plan sea igual de eficiente para el ClienteId 63.

4) Borramos y volvemos a crear el Store Procedure para limpiar el plan de ejecución y ver cuál es el eficiente para el ClienteId 63:

DROP PROCEDURE dbo.GetReservaCliente
GO
CREATE PROCEDURE dbo.GetReservaCliente

@Param2    int
AS

BEGIN

SELECT * FROM Reserva R WHERE R.ClienteId = @Param2
END

GO

Ahora que la cache del Store Procedure fue limpiada, ejecutamos nuevamente el procedimiento y vemos que plan de ejecución genera:

EXEC GetReservaCliente 63


Como podemos observar, el plan más eficiente generado para el ClienteId 63 (1 solo registro) es muy diferente al plan más eficiente para el ClienteId 1065 (28656 registros).

Soluciones cuando hay problemas con Parameter Sniffing:

  • Hacer que el motor regenere el plan de ejecución cada vez que se ejecuta el Store Procedure. Para eso debemos crearlo con la opción WITH RECOMPILE. El costo de hacer esto, es que el SQL Server pierde tiempo en recompilar en vez de usar la cache, pero si este costo es menor al de ejecutar un plan ineficiente, se debe hacer.

CREATE PROCEDURE dbo.GetReservaCliente
@Param2 int
WITH RECOMPILE
AS
BEGIN
SELECT * FROM Reserva R WHERE R.ClienteId = @Param2
END

  • Utilizar el hint RECOMPILE dentro de la query (Solo a partir de SQL Server 2005). Esta solución es similar a la anterior, con la ventaja de que en vez de recompilar todo el store, el SQL Server solo recompila una query especifica. Esto es útil cuando tenemos muchas querys dentro de un mismo Store Procedure y el problema de uso de un plan de ejecución ineficiente refiere a una sola query.

SELECT * FROM Reserva R WHERE R.ClienteId = @Param2 OPTION(RECOMPILE)

  • Deshabilitar Parameter Sniffing usando variables locales. Esto se debe a que SQL Server no tiene en cuenta los valores de las variables locales para hace sniffing, sin embargo, algunos suponen que un futuro esto podría llegar a cambiar, aunque por el momento funciona y es uno de los métodos mas utilizados.

    Ejemplo:

CREATE PROCEDURE dbo.GetReservaCliente
@Param2    int
AS
BEGIN

DECLARE @Param3 int

SET @Param3 = @Param2
SELECT * FROM Reserva R WHERE R.ClienteId = @Param3

END

De este modo, al no utilizar en las consultas del Store Procedure los parámetros propios, al generar el Plan de Ejecución no se utilizan los valores de los parámetros, por lo que en vez de generar el Plan de Ejecución para un caso particular, se generará el Plan de Ejecución genérico para el caso medio.

  • Utilizar el hint OPTIMIZE FOR (Solo a partir de SQL Server 2005). Este hint permite forzar a una consulta a que se ejecute con un plan de ejecución optimizado para un determinado valor.

    SELECT * FROM Reserva R WHERE R.ClienteId = @Param2
    OPTION (OPTIMIZE FOR (@Param2 = 1065))

Este método nos garantiza que para el valor que estamos teniendo problemas va a funcionar bien, aunque podría presentar problemas con otro valores.
Es cuestión de ir probando y ver si vale la pena usarlo. Si el valor típico del parámetro del Store Procedure es compatible con un óptimo plan de ejecución, es una opción interesante.

  • Existen opciones más avanzadas, para casos complejos como por ejemplo crear un propio plan de ejecución usando Plan Guides.


4 comentarios »

  1. HOLA UN SALUDO MUY CORDIAL A TODA LA COMUNIDAD.

    BUENO MAS QUE UN COMENTARIO ES UNA PREGUNTA BUENO SON VARIAS PERO TRATARE DE SER BREVE.

    SELECT FCIA,FTMO,FCTA,FTICOM,FFEC,FNFAC,
    SUM(FMON) AS ABONOF
    From CXP_CAMOV WHERE fcia = txtfcia.text _
    AND FECHA_ABONO <= B0.FECHAF AND SUBSTR(FTMO,1,1) = “A”
    GROUP BY FCIA,FTMO,FCTA,FTICOM,FFEC,FNFAC
    ORDER BY FCIA,FTMO,FCTA,FTICOM,FFEC,FNFAC
    ESTA ES UNA CONSULTA DE UNA TABLA DE MOVIMIENTOS EL CUAL TOTALIZA LOS ABONOS ANTES DE UNA FECHA ESPECIFICA Y DE UN TIPO DE MOVIMIENTO ESPECIFICO
    EL CASO QUE LA TABLA DE MOVIMIENTO TIENE VARIOS CLIENTES Y CADA CLIENTE VARIOS ABONOS PERO EL QUERY TOTALIZA UN SOLO ABONO.
    CUANDO SE EJECUTA EL QUERY QUEDA POCOS REGISTRO LOS CUALES TENGO QUE UTILIZAR PARA ACTULIZAR EN UNA TABLA DE CLIENTES O PROVEEDORES
    LA CUESTION ES COMO SE HARIA EN UN PROCEDIMIENTO ALMACENADO ESTE PROCESO.

    Y COMO SE HARIA DESDE UNA PANTALLA PARA LLAMAR DICHO PROCEDIMIENTO ALMACENA SI EL PROCEDIMIENTO ALMACENADO TENDRIA POR NOMBRE ACTUALIZAZION_SALDOS_CLIENTES Y A DICHO PROCEDIMIENTO SE LE PASARAN 3 PARAMETROS 1 ES EL CODIGO DE COMPAÑIA LA FECHA INICIAL EN CARACTER ‘200090501’, FECHA FINAL ‘20090531’

    SI ME PUEDEN EXPLICAR ESO SE LOS AGRADECERE MUCHO

    EL CASO ES QUE ESTOY APRENDIENDO A USAR VB NET VISUAL STUDIO 2005 CON SQL SERVER 2005 EXPRESS Y NO SE NADA DE PROCEDIMIENTOS ALMACENADOS PERO SE QUE CON UN EJEMPLO PODRE GUIARME.

    GRACIAS POR SU COOPERACION Y QUE DIOS LOS BENDIGA

    Comentario por PEDRO JOSE PINEDA — mayo 6, 2009 @ 7:39 pm

  2. Muy claro, se entiende fácil, buen trabajo.

    Comentario por luis — febrero 23, 2011 @ 11:17 am

  3. Excelente publicacion, me soluciono un gran problema. Muchas gracias

    Comentario por Anónimo — enero 18, 2012 @ 4:21 pm

  4. harga rak gondola jogja

    Parameter Sniffing | Grimpi IT Blog

    Trackback por harga rak gondola jogja — noviembre 18, 2016 @ 10:09 am


RSS feed for comments on this post. TrackBack URI

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

Crea un blog o un sitio web gratuitos con WordPress.com.

A %d blogueros les gusta esto: