Parameter Sniffing en SQL Server. Parte II

Buenas de nuevo amigos! Estamos de vuelta con la segunda entrega acerca de parameter sniffing, hoy vamos a continuar analizando esta casuística y veremos además, alguna de las soluciones a implantar.

Tenemos el mismo escenario que en la entrada anterior (https://www.gpsos.es/2020/05/parameter-sniffing-en-sql-server/). Ahora veremos que pasa si lo ejecutamos en orden inverso, primero ejecutaremos el procedimiento por el filtro 2 y después por el 1.

 exec  dbo.OrderbyReputation @reputation=2
 go
 exec  dbo.OrderbyReputation @reputation=1
 go 

En la anterior entrada vimos que, si ejecutamos ambos procedimientos a la vez, el segundo tomaba el plan del primero, en algunos casos no tendremos problemas, pero veamos que puede ocurrir…

Los planes de ejecución resultantes son los siguientes:

En tiempo, apenas podemos notar la diferencia, pero miremos las lecturas de páginas.

Se han disparado las lecturas de la primera query a 3340157 páginas, hemos tenido que leer 740% más páginas. No podemos olvidar ver que se ha tenido que ejecutar 3345212 veces para devolver todas las filas.

Aunque no lo notemos, este tipo de problemas son un gran ralentizador el motor, por ello te proponemos algunas opciones.

ALGUNAS OPCIONES PARA COMBATIR PARAMETER SNIFFING

No existe un botón mágico que lo solucione ya que cada escenario exige una solución particular, pero, os presentamos a continuación algunas de ellas:

OPTION (RECOMPILE):

Esta opción obliga al SP a generar un nuevo plan para cada ejecución de este por lo que es aconsejable para procedimientos que se ejecuten 2-3 veces por minuto.

 alter procedure dbo.OrderbyReputation
        @reputation int
 AS
 select *
        from dbo.Users
        where Reputation=@reputation
 option (recompile)
 GO 

OPTION (OPTIMEZE FOR UNKNOW)

Este opción obliga al SP a tratar la variable como local, sirviéndose de la propiedad de densidad así como de sampleo de filas:

Desaconsejable para campos donde sea muy dispar la cantidad de datos por valor.

OPTION (OPTIMIZE FOR VALUE)

Funciona correctamente si conocemos bien el modelo de datos y si estos no van a cambiar con el tiempo pues tendremos que ir corrigiendo los SP.

 alter procedure dbo.OrderbyReputation
        @reputation int
 AS
 select *
        from dbo.Users
        where Reputation=@reputation
 option (OPTIMIZE FOR (@reputation = 1))
 GO 

Estas son algunas de las opciones que tenemos para lidiar con estos problemas, si necesitas ayuda con este o algún otro problemilla, no dudes en contactar con nosotros.

Si no quieres perderte entradas como esta, suscríbete a nuestra newsletter. Con un solo email al mes estarás informado.

¿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

Más información: https://docs.microsoft.com/es-es/sql/?view=sql-server-ver15