GuilleSQL :: Microsoft SQL Server, SSIS, y más !!

Procedimiento Almacenado lento, pero su Consulta es rápida ¿Por qué ocurre? ¿Por el Plan de Ejecución? ¿Por las Estadísticas? ¿Es Parameter Sniffing?


Este Artículo presenta un problema típico asociado a los Procedimientos Almacenados con Parámetros y la generación del Plan de Ejecución (compilación) y la reutilización del Plan de Ejecución generado (pues el Plan de Ejecución se almacena en caché y se intenta reutilizar). Se trata del problema de Parameter Sniffing. Del mismo modo, se explican distintas soluciones: utilizar variables locales (DECLAREs), utilizar SQL Dinámico con sp_executesql ó EXEC(@sql), utilizar WITH RECOMPILE al invocar al Procedimiento Almacenado, utilizar la sugerencia de consulta WITH(RECOMPILE), utilizar la sugerencia de consulta WITH(OPTIMIZE FOR), utilizar la sugerencia de consulta USE PLAN, utilizar Plan Guides (sp_create_plan_guide), etc.

Un Problema de Rendimiento en SQL Server relativamente habitual (perdón, una situación relativamente habitual ;-) al trabajar con Procedimientos Almacenados en SQL Server, es que un Procedimiento Almacenado presenta un tiempo de ejecución muy lento. Este comportamiento, que podríamos entender normal (bajo circunstancias tales como existencia de bloqueos, alta carga del servidor SQL Server, etc.), deja de parecérnoslo cuando al ejecutar directamente (es decir, desde una nueva ventana de consulta, sin Procedimiento Almacenado, ni naa) la misma consulta que la utilizada en el Procedimiento Almacenado (y en las mismas condiciones), el tiempo de ejecución es muy rápido. ¿A qué es debido éste comportamiento?

El tiempo de ejecución de una consulta, puede presentar desviaciones por diferentes motivos, entre los cuales citamos algún ejemplo (ojo, que existen muchos más motivos):

  • Esperas por existencia de bloqueos, que en la mayoría de las situaciones se puede eliminar o disminuir sensiblemente utilizando lecturas sucias (SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED).
  • Ausencia de índices que satisfagan de forma suficiente la resolución de la consulta.
  • Estadísticas de distribución de índices desactualizadas, que impliquen la generación y utilización de un Plan de Ejecución poco óptimo (por ejemplo, porque el Optimizador de Consultas pueda realizar una mala elección del índice apropiado, por culpa de dichas estadísticas).
  • El servidor SQL Server está sobrecargado, ya sea en tiempo de procesador (CPU), acceso a disco (E/S), y/o disponibilidad de memoria RAM.
  • La consulta devuelve un conjunto de resultados grande y la red está sobrecargada, actuando la propia LAN como un cuello de botella (ya sea porque las líneas de comunicación del servidor SQL Server están saturadas, porque los conmutadores de red – switches – están saturados en CPU o en ancho de banda, etc.).

Sin embargo, el caso al que nos referimos en el presente artículo es diferente ¿Cómo puede ser que la misma consulta, en las mismas condiciones (de bloqueos, nivel de aislamiento, existencia de índices y estadísticas, carga del servidor y de la red, etc.) pueda presentar un tiempo de ejecución excesivamente lento o excesivamente rápido, simplemente ejecutando la consulta desde un Procedimiento Almacenado o directamente desde una ventana de consulta? Aquí va una posible explicación (el Plan de Ejecución y Parameter Sniffing) que me he encontrado recientemente al trabajar con Procedimientos Almacenados con Parámetros, y que en ocasiones resulta ser el motivo:

Al menos en el caso que se me presentó, se trataba de un problema conocido como Parameter Sniffing, que ocurre con los Procedimientos Almacenados que tienen Parámetros y que utilizan dichos Parámetros en sus consultas.

El concepto de Parameter Sniffing se refiere al hecho de que SQL Server obtiene (snif) los valores de los parámetros con que es invocado el procedimiento almacenado, y se los facilita al Optimizador de Consultas, para que éste genere el Plan de Ejecución más apropiado (en función de los índices existentes, estadísticas de distribución, y los valores de los parámetros de la consulta, principalmente), siempre y cuando se produzca el escenario descrito, esto es, que la consulta (o consultas) ejecutada en el Procedimiento Almacenado utilice los parámetros del Procedimiento Almacendo para filtrar sus datos (ej; en la cláusula WHERE). Este comportamiento es así desde SQL Server 7.0 (en SQL Server 6.5 no se producía Parameter Sniffing).

Recordar que el Plan de Ejecución NO se genera con la creación del Procedimiento Almacenado, sino que por el contrario, se crea y se cachea en la primera ejecución del Procedimiento Almacenado (excepto en aquellas ocasiones que se recompila el Procedimiento Almacenado, esto es, se genera un nuevo Plan de Ejecución).

Por ello, en la primera llamada al procedimiento almacenado, se genera el Plan de Ejecución y se almacenará en memoria. Esto implica, que en la segunda y sucesivas llamadas al Procedimiento Almacenado, se reutilizará el Plan de Ejecución (bueno... mejor dicho, se intentará reutilizar ;-). Sin embargo, puede ocurrir que el Plan de Ejecución en caché NO sea tan eficiente para otras llamadas al Procedimiento Almacenado con otros valores para los Parámetros de entrada, pudiendo caer el rendimiento considerablemente. Del mismo modo, que puede ocurrir que los valores de los parámetros utilizados en la primera invocación al Procedimiento Almacenado sean valores típicos, esto es, sean valores representativos (por su distribución en las columnas de las tablas) y el Plan de Ejecución generado sea bien aprovechado (es decir, óptimo) para futuras invocaciones del Procedimiento Almacenado. De hecho, existen más problemas debidos a la reutilización de Planes de Ejecución en SQL Server, como por ejemplo, el relativo a los eventos Hash Warnings y Sort Warning.

Para solucionar éste problema (deshabilitar Parameter Sniffing) existen varias alternativas:

  • Utilizar en los Procedimientos Almacenados variables locales, asignar a estas variables locales los Parámetros de entrada de los Procedimientos Almacenados, y sustituir en las consultas los Parámetros por las variables locales. De este modo, al no utilizar en las consultas del Procedimiento Almacenado los Parámetros del propio Procedimiento Almacenado, 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 (que será reutilizado por el resto de invocaciones al Procedimiento Almacenado), se generará el Plan de Ejecución para el caso medio (es decir, se utilizará la densidad media de las columnas filtradas para la elección de índices, etc.).
  • Sustituir las consultas del Procedimiento Almacenado por SQL Dinámico, ya sean llamadas a sp_executesql o EXEC(@sql). En función de cómo se use el SQL Dinámico, existe riesgo de SQL Injection (ojo, que lo de Injection no es por que sea más rápido... que no... que es un agujero de seguridad del carajo... ;-)
  • Utilizar WITH RECOMPILE al invocar al Procedimiento Almacenado (ej: EXEC miProc @p1=23 WITH RECOMPILE;). Esto producirá que se genere un nuevo Plan de Ejecución en la llamada al Procedimiento Almacenado, con indiferencia de si existía o no un Plan de Ejecución en caché para el mismo. Una alternativa puede ser utilizar el Procedimiento Almacenado del Sistema sp_recompile, especificando el nombre del objeto que deseamos que se recompile (es decir, que se vuelva a generar su Plan de Ejecución en la próxima invocación).
  • Utilizar la sugerencia de consulta (query hint) RECOMPILE. Tiene la ventaja, de que en caso de tener un Procedimiento Almacenado con múltiples consultas, podemos utilizar esta sugerencia sólo en la consulta en la que deseemos volver a generar el Plan de Ejecución, de tal modo, que no será necesario volver a generar el Plan de Ejecución del resto de consultas del Procedimiento Almacenado. Esta posibilidad está disponible desde SQL Server 2005, utilizando en las consultas la sugerencia de consulta OPTION(RECOMPILE). Ej: SELECT … FROM … WHERE … OPTION(RECOMPILE). Requiere modificar la consulta, y en consecuencia, el Procedimiento Almacenado.
  • Utilizar la sugerencia de consulta (query hint) OPTIMIZE FOR. De este modo, podemos forzar que se ejecute el Plan de Ejecución para una consulta del Procedimiento Almacenado, optimizado para un valor determinado. Esta posibilidad está disponible desde SQL Server 2005, utilizando en las consultas la sugerencia de consulta OPTION(OPTIMIZE FOR). Ej: SELECT ... FROM ... WHERE ... OPTION(OPTIMIZE FOR (@ParamId = ‘MAD’)). Requiere modificar la consulta, y en consecuencia, el Procedimiento Almacenado.
  • Utilizar la sugerencia de consulta (query hint) USE PLAN. Requiere facilitar a la consulta el Plan de Ejecución que se desea que utilice, en formato XML (se puede obtener el Plan de Ejecución en formato XML utilizando SET SHOWPLAN_XML, pudiendo modificar el Plan de Ejecución deseado para obtener el comportamiento deseado). Esta posibilidad está disponible desde SQL Server 2005. Requiere modificar la consulta, y en consecuencia, el Procedimiento Almacenado.
  • Utilizar Plan Guides (sp_create_plan_guide). Desde SQL Server 2005 es posible utilizar los Plan Guides, un método para asociar a una consulta un Plan de Ejecución determinado, o también para asociar a una consulta sugerencias de consulta determinadas, en ambos casos, sin necesidad de alterar la consulta. Se utiliza a través del procedimiento almacenado sp_create_plan_guide.

Me he encontrado con programadores, que cuando se encuentran con problemas de éste tipo, eliminan el Procedimiento Almacenado (DROP PROCEDURE) y lo vuelven a crear (CREATE PROCEDURE). Esto puede ser una solución, ya que de éste modo, también limpiamos de caché el Plan de Ejecución (y tanto... limpiamos hasta el Procedimiento Almacenado... jeje ;-), pero antes de tomar ésta medida, creo que vale la pena investigar un poco más el problema particular, y ofrecer una solución definitiva (en vez de una solución temporal - WorkAround - de eliminar y crear el Procedimiento Almacenado de vez en cuando, sin comprender el motivo y sin saber cuando se volverá a reproducir el problema...). Apaguemos el fuego, pero del todo, ¿no?.

En mi caso, tenía Procedimientos Almacenados con Parámetros, que dentro del cuerpo del Procedimiento Almacenado, se modificaba el valor de dichos Parámetros de entrada, como se muestra en el siguiente ejemplo (el caso real utilizaba múltiples parámetros y una consulta con JOINs entre múltiples tablas):

CREATE PROCEDURE dbo.getEmployees
   @DeptId AS VARCHAR(5)
AS
   IF @DeptId = '0' SET @DeptId = NULL

   SELECT ... FROM ... WHERE ... (DeptId=@DeptId OR @DeptId IS NULL)

Para solucionarlo, cambié el Procedimiento Almacenado para ser similar al siguiente ejemplo:

CREATE PROCEDURE dbo.getEmployees
   @DeptId AS VARCHAR(5)
AS
   DECLARE @pDeptId VARCHAR(5)

   SET @pDeptId = @DeptId

   IF @pDeptId = '0' SET @pDeptId = NULL

   SELECT ... FROM ... WHERE ... (DeptId=@pDeptId OR @pDeptId IS NULL)

La explicación es muy sencilla. Los valores utilizados en las sentencias IF al principio del Procedimiento Almacenado, indican que se desean todos los valores posibles, por ello se establece a NULL el Parámetro en dicho caso, y en la propia consulta se debilita la cláusula WHERE con un operador OR y una comparación IS NULL sobre el valor del Parámetro. Esto implica, que al pasar el valor 0 al Procedimiento Almacenado, el Optimizador de Consultas elige un acceso por índice (es lo más lógico al utilizar un único valor sobre un campo indexado, que es lo que se piensa el Optimizador de Consultas), de tal modo, que al cambiar el valor del Parámetro por NULL se van a devolver todas las filas, por lo que realmente se necesita recorrer toda la tabla, pero en vez de realizar una lectura completa de tabla (lo lógico, si se desea leer toda la tabla), se realiza un acceso por índice (por culpa de Plan de Ejecución generado) y para cada valor de índice se realiza una búsqueda (Lookup) a la tabla, lo cual resulta en algo mucho más costoso.

Por ello, en mi caso la mejor solución era utilizar variables locales (DECLAREs), y desvincular los Parámetros de la Consulta. La mejora experimentada, fue reducir el tiempo de ejecución en algunos casos de cerca de una hora a menos de 10 segundos (simplemente utilizando variables locales en la consulta del Procedimiento Almacenado... sin tocar índices, ni nada más). Otros casos, fueron mucho más modestos, y en algunos otros casos no se producía mejora (es decir, que esto no es la solución a todas nuestras penas... sólo a algunas de ellas ;-)

Una vez más, espero que os sirva como a mí me ha servido !




Miembros de
Miembros de GITCA (Global IT Community Association)

Menu de Usuario
  Iniciar Sesión
  Registrarse
  Restablecer Contraseña
  Ventajas de Registrarse

Acerca de
  Contigo desde Oct 2007
  771 usuarios registrados
  86146 pageloads/mes
  Ranking Alexa 498160

Social Networks
Sigue a Portal GuilleSQL en Linkedin !!
Sigue a Portal GuilleSQL en Twitter !!



Archivo

Marzo de 2019 (1)
Octubre de 2018 (1)
Julio de 2018 (1)
Junio de 2018 (4)
Mayo de 2018 (5)
Abril de 2018 (3)
Marzo de 2018 (2)
Febrero de 2018 (7)
Enero de 2018 (1)
Diciembre de 2017 (15)
Noviembre de 2017 (7)
Junio de 2017 (3)
Mayo de 2017 (1)
Marzo de 2017 (3)
Enero de 2017 (4)
Junio de 2016 (1)
Mayo de 2016 (2)
Abril de 2016 (2)
Septiembre de 2015 (2)
Agosto de 2015 (2)
Junio de 2015 (10)
Mayo de 2015 (4)
Abril de 2015 (8)
Marzo de 2015 (11)
Octubre de 2014 (3)
Septiembre de 2014 (7)
Agosto de 2014 (5)
Julio de 2014 (2)
Mayo de 2014 (4)
Abril de 2014 (4)
Marzo de 2014 (4)
Febrero de 2014 (1)
Enero de 2014 (5)
Diciembre de 2013 (8)
Noviembre de 2013 (2)
Octubre de 2013 (7)
Septiembre de 2013 (6)
Agosto de 2013 (1)
Julio de 2013 (6)
Junio de 2013 (11)
Mayo de 2013 (7)
Abril de 2013 (6)
Febrero de 2013 (5)
Enero de 2013 (7)
Diciembre de 2012 (12)
Noviembre de 2012 (13)
Octubre de 2012 (5)
Septiembre de 2012 (3)
Agosto de 2012 (6)
Julio de 2012 (4)
Junio de 2012 (1)
Mayo de 2012 (2)
Abril de 2012 (7)
Marzo de 2012 (16)
Febrero de 2012 (9)
Enero de 2012 (5)
Diciembre de 2011 (10)
Noviembre de 2011 (10)
Octubre de 2011 (4)
Septiembre de 2011 (5)
Agosto de 2011 (2)
Julio de 2011 (2)
Junio de 2011 (4)
Mayo de 2011 (2)
Abril de 2011 (6)
Marzo de 2011 (4)
Febrero de 2011 (10)
Enero de 2011 (5)
Diciembre de 2010 (6)
Noviembre de 2010 (4)
Octubre de 2010 (8)
Septiembre de 2010 (4)
Agosto de 2010 (1)
Julio de 2010 (3)
Mayo de 2010 (5)
Abril de 2010 (6)
Marzo de 2010 (8)
Febrero de 2010 (3)
Enero de 2010 (1)
Diciembre de 2009 (9)
Noviembre de 2009 (14)
Octubre de 2009 (2)
Septiembre de 2009 (8)
Agosto de 2009 (2)
Julio de 2009 (10)
Junio de 2009 (9)
Mayo de 2009 (10)
Abril de 2009 (9)
Marzo de 2009 (3)
Febrero de 2009 (2)
Enero de 2009 (3)
Noviembre de 2008 (2)
Octubre de 2008 (2)
Septiembre de 2008 (2)
Agosto de 2008 (5)
Julio de 2008 (5)
Junio de 2008 (1)
Mayo de 2008 (3)
Abril de 2008 (2)
Marzo de 2008 (2)
Febrero de 2008 (2)
Enero de 2008 (5)
Noviembre de 2007 (2)
Octubre de 2007 (2)






Copyright © 2007 GuilleSQL, todos los derechos reservados.