Problemas de rendimiento en procedimientos almacenados con parámetros - Parameter Sniffing

En SQL Server los procedimientos almacenados tienen un comportamiento por defecto , se llama parameter sniffing.

Cuando un procedimiento almacenado es compilado o recompilado y se ejecuta por primera vez, el valor de los parámetros que recibe es utilizado para calcular el plan de ejecución más óptimo y este es almacenado en cache. En futuras ejecuciones el plan de ejecución no es calculado, sino que se utiliza el almacenado en cache según el valor de los parámetros de la primera ejecución después de la compilación.


Este comportamiento no es un problema en la mayoría de los casos, ya que el calculo del plan de ejecución lleva un tiempo en realizarse y si nos lo ahorramos porque esta en cache, más rápido va a ejecutar el procedimiento almacenado. ¿Entonces cuando es un problema?, cuando la cantidad de registros que tiene que tratar el procedimiento almacenado varia en gran medida en función de los parámetros recibidos y por tanto el plan de ejecución más óptimo en cada caso es diferente.

Por ejemplo si un procedimiento almacenado debe comprobar para devolver su resultado los productos de una tienda y hay tiendas que tienen 2 millones de productos y otras 100, la cantidad de registros a evaluar cambia mucho y el plan de ejecución para un caso y otro va a ser diferente. Si el plan de ejecución almacenado en cache es para el valor de los parámetros de 100 productos, cuando ejecutemos el procedimiento almacenado para el caso de 2 millones, el tiempo de respuesta va a ser muy lento, porque el plan de ejecución no esta optimizado para este caso sino para otro más sencillo.

Veamos unos ejemplos concretos de la base de datos AdventureWorks que podemos descargar aquí.

Si hacemos una query a la tabla Sales.SalesOrdeDetail filtrando por productos donde vamos a tener diferentes resultados y vemos el plan de ejecución de cada uno

La primera query es:

SELECT *
  FROM [AdventureWorks2008R2].[Sales].[SalesOrderDetail]
  where ProductID = 870

el número de registros devueltos son 4688 y el plan de ejecución en este.

La segunda query es:

SELECT *
  FROM [AdventureWorks2008R2].[Sales].[SalesOrderDetail]
  where ProductID = 897

el número de registros devueltos son 2 y el plan de ejecución en este.

El plan de ejecución más óptimo para cada query es diferente. Si creamos un procedimiento almacenado.

CREATE PROCEDURE GetSalesOrderDetail @ProductID INT
AS
BEGIN
 SELECT *
  FROM [AdventureWorks2008R2].[Sales].[SalesOrderDetail]
  where ProductID = @ProductID
END
GO

Y lo ejecutamos con los mismos valores que antes.

Primero el que devolverá 2 registros

exec GetSalesOrderDetail 897

el plan de ejecución es este

Después el que devolverá 4688 registros

exec GetSalesOrderDetail 870

el plan de ejecución es este

El plan de ejecución es el mismo con ambos valores, es decir, para el segundo valor, el que devuelve mas registros no se esta utilizando el plan más óptimo, porque está usando el plan de ejecución almacenado en cache con la primera ejecución. Con este volumen de registros puede no existir mucha diferencia de rendimiento cuando esto se produce pero con diferencias mucho más grandes el empeoramiento de rendimiento puede ser muy notable.
¿Como podemos evitar parameter sniffing?, hay varias formas.

Recompilar el procedimiento almacenado con cada ejecución

Si al procedimiento almacenado le añadimos la intrucción with recompile, en cada ejecución calculará el plan de ejecución más óptimo para los parámetros actuales. Esta solución se debe aplicar cuando el procedimiento almacenado no se ejcuta muy a menudo porque el calculo del plan de ejecución es costoso.
O por lo menos se debe aplicar si da mejor rendimiento que utilizar parameter sniffing aunque se ejecute a menudo

CREATE PROCEDURE GetSalesOrderDetail @ProductID INT
WITH RECOMPILE
AS
BEGIN
 SELECT *
  FROM [AdventureWorks2008R2].[Sales].[SalesOrderDetail]
  where ProductID = @ProductID
END
GO

Utilizar OPTIMIZE FOR

Si después de las pruebas hay algún valor de parámetro que da un rendimiento aceptable en todos los casos, se puede utilizar la instrucción OPTIMIZE FOR.

ALTER PROCEDURE GetSalesOrderDetail @ProductID INT
AS
BEGIN

 SELECT *
  FROM [AdventureWorks2008R2].[Sales].[SalesOrderDetail]
  where ProductID = @ProductID
  OPTION (OPTIMIZE FOR (@ProductID = 870));
END
GO

El plan de ejecución que se almacena en cache y es utilizado es para el ProductID 870 en posteriores ejecuciones

Utilizar OPTIMIZE FOR UNKNOWN

Le indica al optimizador de query que utilice valores estadísticos para calcular el plan de ejecución y no un valor inicial de ningún parámetro, como consecuencia genera un plan de ejecución más estándar.

ALTER PROCEDURE GetSalesOrderDetail @ProductID INT
AS
BEGIN

 SELECT *
  FROM [AdventureWorks2008R2].[Sales].[SalesOrderDetail]
  where ProductID = @ProductID
  OPTION (OPTIMIZE FOR UNKNOWN);
END
GO

Utilizar variables locales

Consiste en declarar tantas variables como parámetros en el procedimiento y que sean estas variables las usadas en la query, de esta forma no se produce Parameter sniffing, como con OPTIMIZE FOR UNKNOW, el plan de ejecución será el más estándar.

Utilizar Sql dinámico para la query

Una opción que va a funcionar como si se ejecutarán las queries directamente, es usar execute para ejecutar la query dinámicamente.

ALTER PROCEDURE GetSalesOrderDetail @ProductID INT
AS
BEGIN

exec ('SELECT *   FROM [AdventureWorks2008R2].[Sales].[SalesOrderDetail]
  where ProductID =' + @ProductID)
END
GO

Hay que tener en cuenta que parameter sniffing también se produce si se utiliza el procedimiento almacenado sp_executesql con parametros, por lo tanto si tienes un problema de rendimiento provocado por este comportamiento por defecto de Sql Server, la solución no va ser ejecutar dentro de tu procedimiento almacenado la query usando sp_executesql con parámetros porque tiene el mismo comportamiento.

Libros Relacionados

---