Caché de planes de ejecución en SQL Server

En el artículo anterior, vimos cómo funcionan los planes de ejecución en SQL Server y cómo podemos analizarlos para optimizar nuestras consultas. Hoy vamos a profundizar en un aspecto muy importante de los planes de ejecución: la caché de planes.

La caché de planes es el espacio de memoria donde SQL Server almacena los planes de ejecución que ha generado previamente, para poder reutilizarlos si se vuelve a ejecutar la misma consulta. Esto tiene varias ventajas, como evitar el coste de generar un nuevo plan cada vez, y facilitar el uso de parámetros en las consultas.

¿Cómo ver los planes en caché?

Para ver los planes de ejecución almacenados en caché usaremos la vista de sistema sys.dm_exec_cached_plans y nos ayudaremos de las funciones sys.dm_exec_sql_text y sys.dm_exec_query_plan para ver el texto de la consulta y el plan de ejecución en formato XML. Está será la consulta que usaremos:

SELECT cplan.usecounts, cplan.objtype, qtext.text, qplan.query_plan
FROM sys.dm_exec_cached_plans AS cplan
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS qtext
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qplan
ORDER BY cplan.usecounts DESC

¿Cómo se almacenan los planes en caché?

Los más avispados os habréis dado cuenta que el plan de ejecución se almacena usando el campo plan_handle, este corresponde a un hash de la consulta que estamos ejecutando. Esto quiere decir que cualquier mínima variación en el texto de la consulta generará un nuevo plan que se almacenará en la caché, aunque la consulta sea la misma y solo hayamos añadido espacios o tabuladores. Esto puede ser un problema y tenemos que tenerlo en cuenta ya que puede ser que almacenemos multitud de planes en caché para una misma consulta.

¿Qué pasa si la caché se llena y no hay espacio para nuevos planes?

Si la caché se llena, SQL Server empieza a eliminar los planes más antiguos o menos usados, siguiendo un algoritmo llamado LRU (least recently used). Esto puede provocar que se pierdan planes óptimos y que se tengan que generar nuevos planes cada vez, lo que afecta al rendimiento. Para evitarlo, podemos aumentar el tamaño de la caché, usando la opción ‘max server memory’, o podemos limpiar la caché manualmente, usando el comandos DBCC. Tenemos que evitar vaciar completamente la caché en nuestros entornos de producción pues todos las nuevas consultas tendrán que compilar de nuevo sus planes de ejecución consumiendo más recursos. También tendremos que tener en cuenta que al ser una caché en memoria no es persistente, se borrará completamente con cada reinicio del servicio.

¿Puede ser que los planes de la caché no sean los más adecuados para las consultas actuales?

Si los datos han sufrido una variación tal que los planes en caché no son los más adecuados para las consultas actuales, podemos tener un problema de recompilación o de parámetro erróneo. La recompilación ocurre cuando SQL Server detecta que el plan existente no es válido o no es óptimo para la consulta actual, y genera uno nuevo. Esto puede ser beneficioso en algunos casos, pero también puede consumir recursos y tiempo. El parámetro erróneo ocurre cuando SQL Server usa el mismo plan para consultas con diferentes valores de parámetros, lo que puede provocar que el plan no sea eficiente para algunos valores. Para evitar estos problemas, podemos usar la opción ‘optimize for’ o ‘recompile’ en las consultas, o podemos usar procedimientos almacenados con parámetros.

¿Qué pasa si hay demasiados planes similares que ocupan espacio innecesariamente?

Si hay demasiados planes similares que ocupan espacio innecesariamente, podemos tener un problema de crecimiento de la caché. Esto ocurre cuando SQL Server genera planes diferentes para consultas que solo cambian en los valores literales, lo que hace que la caché se llene de planes redundantes. Para evitarlo, podemos usar parámetros en las consultas, o podemos usar la opción ‘forced parameterization’ en las bases de datos.

Uso de procedimientos almacenados para optimizar la caché

Una forma de reducir el impacto de estos problemas es usar procedimientos almacenados en lugar de consultas ad hoc. Los procedimientos almacenados son bloques de código SQL que se almacenan en la base de datos y se pueden ejecutar con diferentes valores de parámetros. 

Los procedimientos almacenados tienen varias ventajas respecto a las consultas ad hoc:
– Solo generan un plan de ejecución por cada procedimiento almacenado, independientemente del valor de los parámetros. Esto evita la inflación de la caché y el parámetro erróneo.
– Solo recompilan el plan si hay cambios en el código del procedimiento almacenado o en la estructura de las tablas. Esto evita la recompilación innecesaria y mejora el rendimiento.
– Permiten encapsular la lógica de negocio y facilitar el mantenimiento y la seguridad del código.

Sin embargo, los procedimientos almacenados también tienen algunos inconvenientes:
– Pueden ser más difíciles de escribir y depurar que las consultas ad hoc.
– Pueden tener problemas de rendimiento si el plan generado no es óptimo para todos los valores posibles de los parámetros. Desde SQL 2019 esto se soluciona en gran medida gracias al uso del componente adaptive join al persistir los planes en caché. En versiones anteriores de SQL tendríamos que recurrir a usar la opción ‘recompile’ o ‘optimize for’ en nuestro procedimiento.
– Pueden tener problemas de seguridad si no se usan correctamente las opciones ‘execute as’ o ‘with encryption’.

Conclusión

La caché de planes es un elemento clave para el rendimiento de SQL Server, ya que permite reutilizar los planes de ejecución y ahorrar recursos. Pero también hay que tener cuidado con los posibles problemas que puede causar, como la pérdida de memoria, los planes ineficientes o la inconsistencia de datos. Para evitarlos, podemos usar herramientas y opciones de configuración que nos permitan controlar y optimizar la caché de planes. Además, podemos usar procedimientos almacenados en lugar de consultas ad hoc, para reducir el número de planes generados y mejorar la calidad del código.

Si quieres convertirte en un experto en planes de ejecución y mejorar el rendimiento de tus bases de datos, no te pierdas los siguientes artículos. Y si tienes alguna duda o comentario, no dudes en escribirme aquí abajo, en Twitter o por mail. Estaré encantado de ayudarte.