¡¡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.
Respecto a las lecturas vemos: 44530 páginas vs 5694
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.
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.
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.