PARAMETER SNIFFING EN SQL SERVER

Públicado en por

¡¡Buenas de nuevo!!  Hoy vamos a ver cómo afecta la compilación con diferentes parámetros a SQL Server dentro de Stored procedures. Para ellos usaremos la técnica del parameter sniffing. Con la cual, haremos de detectives para averiguar por qué una query se comporta de forma diferente en función de los parámetros que le pasemos.

En el siguiente ejemplo vemos que, aunque tenemos la misma query, si ponemos diferentes parámetros SQL Server obtiene 2 planes de ejecución diferentes, un síntoma claro podemos verlo si la query a veces necesita toneladas de recursos y a veces apenas los requiere.

Además, podremos ver lo importante que es conocer el modelo de datos a la hora de trabajar.

Trabajamos con la BBDD Libre de StackOverflow, en concreto con la tabla dbo.Users. Ésta cuenta con 5 millones de usuarios.

Escenario para el parameter sniffing

Tenemos las siguientes querys:

 select *
        from dbo.Users
        where Reputation='1';
 GO
  
 select *
        from dbo.Users
        where Reputation='2';
 GO

Si ejecutamos las querys vemos que tienen planes de ejecución diferentes, la primera realiza un full de cluster index ya que estima que necesitará sobre 1M de filas. En cambio, el segundo realiza index seek y key lookup puesto que solo devolverá unas 1800 filas.

parameter sniffing

Respecto a las lecturas vemos: 44530 páginas vs 5694

parameter sniffing

Este no representa ningún problema, al contrario, SQL Server cambia de plan si ve mejores condiciones de búsqueda. Pero…¿Qué pasa si realizamos estas querys a través de un procedimiento?, ¡veamos!

 create procedure dbo.OrderbyReputation
        @reputation int
 AS
 select *
        from dbo.Users
        where Reputation=@reputation;
 GO

Procedemos a ejecutar el SP con los parámetros 1 y 2:

exec 
dbo.OrderbyReputation @reputation=1

exec  
dbo.OrderbyReputation @reputation=2
go

y analizamos los planes de ejecución.

parameter sniffing

Ambas queries han tomado el plan de ejecución de la primera query. Para la segunda es un problema pues consume más de lo que necesita y si la ejecutamos miles de veces en paralelo estaremos desaprovechando memoria y CPU. Además, han aumentado las lecturas significativamente.

parameter sniffing

De 5964 a 44530 páginas leídas en cada ejecución, eso hace un computo total de 40000 páginas leídas de más cada vez.

Esta, puede ser una de las razones por las que tu PLE tiene caídas bruscas “inexplicables” pero como hemos visto, todo tiene una razón.

Hasta aquí la entrada de hoy! Ya sabes como detectarlo, si quieres ver como lidiar con parameter sniffing, no te pierdas la siguiente entrada! Hasta la próxima!

Si no quieres perderte ninguna, suscríbete a nuestra newsletter. Con un solo email al mes, estarás informado de todas nuestras publicaciones.

¿Aún no conoces Query Performance? Descubre cómo puede ayudarte en tu entorno Oracle. Más información en su página de LinkedIn.

Sígue a GPS en LinkedIn