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?

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):

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

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 !