En este artículo vamos a hablar de una opción de SQL Server que puede mejorar el rendimiento de las consultas ad hoc, es decir, aquellas que se generan dinámicamente y no se repiten con frecuencia. Se trata de la opción OPTIMIZE FOR AD HOC WORKLOADS, que modifica el comportamiento del caché de planes de ejecución. El objetivo de esto es reducir el consumo de recursos de nuestro servidor como vamos a ver a continuación.
Antes de optimizar para Ad Hoc
Para poder saber si esta es una solución ideal para nosotros tenemos que tener claro una serie de conceptos previos. Estos son los planes de ejecución y la caché de planes. Nosotros los hemos tratado ya en los anteriores artículos en el blog pero aún así vamos a revisarlos.
Como sabemos, los planes de ejecución son la forma en la que el motor de base de datos ejecuta las consultas. Son una guía paso a paso dividida en componentes atómicos que dicen todo lo que va a necesitar hacer SQL Server para devolvernos los datos solicitados.
Como también sabemos, la caché de planes de ejecución es una estructura de memoria que almacena los planes generados por el optimizador para las consultas que se ejecutan en el servidor. Esto permite reutilizar los planes y ahorrar el coste de generarlos cada vez que se ejecuta una consulta. Sin embargo, la caché de planes tiene un tamaño limitado y puede llenarse rápidamente si se ejecutan muchas consultas diferentes. En ese caso, el servidor tiene que eliminar los planes más antiguos o menos usados para hacer espacio a los nuevos, lo que se conoce como recompilaciones. Las recompilaciones pueden afectar al rendimiento del servidor y consumir recursos de CPU y memoria.
Activar la optimización para consultas Ad Hoc
La opción OPTIMIZE FOR AD HOC WORKLOADS ayuda a reducir el impacto de las consultas ad hoc en la caché de planes. Lo que hace es almacenar solamente una parte del plan (un stub) la primera vez que se ejecuta una consulta ad hoc, en lugar del plan completo. Esto ocupa menos espacio en la caché y permite almacenar más planes. Si la consulta se vuelve a ejecutar, entonces se almacena el plan completo y se puede reutilizar. De esta forma, se evita llenar la caché con planes que solo se usan una vez y se favorece la reutilización de los planes que se repiten.
Para activar esta opción, se puede usar el siguiente comando a nivel de base de datos:
ALTER DATABASE SCOPED CONFIGURATION SET OPTIMIZE_FOR_AD_HOC_WORKLOADS = ON;Esta opción solo afecta a la base de datos actual y se puede activar o desactivar en cualquier momento. Sin embargo, también existe la posibilidad de configurar esta opción a nivel de instancia, lo que afecta a todas las bases de datos del servidor. Para ello, se puede usar el siguiente comando:
sp_configure 'optimize for ad hoc workloads', 1;
RECONFIGURE;Al activar esta opción a nivel de instancia, podemos aplicar los beneficios de la optimización para consultas ad hoc a todo el servidor, sin tener que hacerlo individualmente para cada base de datos. Esto puede ser útil cuando tenemos un entorno con muchas bases de datos y muchas consultas dinámicas.
¿Merece la pena activar la optimización para Ad Hoc?
Ahora bien, ¿cuándo debemos activar esta opción y cuándo no? La respuesta depende del tipo de carga de trabajo que tengamos en nuestro servidor. Si tenemos un entorno con pocas consultas ad hoc y muchas consultas repetitivas, entonces no es necesario activar esta opción, ya que no tendremos problemas con el caché de planes. Por el contrario, si tenemos un entorno con muchas consultas ad hoc y pocas consultas repetitivas, entonces sí es conveniente activar esta opción, ya que podremos ahorrar espacio en el caché y evitar recompilaciones innecesarias.
Pero ¿cómo saber el número de consultas ad hoc que tenemos en nuestro servidor? Una forma sencilla es usar la vista dinámica de gestión sys.dm_exec_cached_plans, que nos muestra los planes almacenados en el caché. Podemos filtrar por el tipo de objeto ‘Adhoc’ y contar cuánta cache ocupan estos planes. Por ejemplo:
WITH CTE_PLANS AS (
SELECT SUM(CASE
WHEN objtype = 'adhoc'
THEN convert(float,size_in_bytes)
ELSE 0 END) / 1048576.0 AdHoc_Plan_MB,
SUM(convert(float,size_in_bytes)) / 1048576.0 Total_Cache_MB
FROM sys.dm_exec_cached_plans)
SELECT AdHoc_Plan_MB, Total_Cache_MB, AdHoc_Plan_MB *100.0 / Total_Cache_MB AS [AdHoc %]
FROM CTE_PLANSEste comando nos devuelve el espacio consumido por planes ad hoc en la caché. Podemos compararlo con el espacio total de los planes en caché para ver qué porcentaje representan. Si es mayor al 25%, entonces podemos considerar activar la opción OPTIMIZE FOR AD HOC WORKLOADS. Tened en cuenta que los datos de la vista sys.dm_exec_cached_plans se reinician con cada reinicio del servicio de SQL Server por lo que antes de ejecutarlo debemos asegurarnos que el servidor lleva encendido bastante tiempo para tener datos fiables.
Conclusión
En este artículo hemos visto cómo la opción OPTIMIZE FOR AD HOC WORKLOADS puede mejorar el rendimiento de las consultas ad hoc en SQL Server. Como hemos comentado en la introducción también vamos a lograr reducir el consumo de recursos. Esta opción es especialmente útil cuando tenemos un entorno con muchas consultas dinámicas que no se repiten con frecuencia y que pueden llenar el caché de planes rápidamente. Al activar esta opción, podemos reducir el espacio ocupado por los planes en el caché (consumo de memoria RAM), evitar las recompilaciones innecesarias (Consumo de CPU) y favorecer la reutilización de los planes que se ejecutan más de una vez (Consumo de CPU).
Además, hemos visto cómo podemos configurar esta opción a nivel de base de datos o a nivel de instancia, según nuestras necesidades. También hemos visto cómo podemos saber el número de consultas ad hoc que tenemos en nuestro servidor y cuándo debemos activar o no esta opción. Esperamos que este artículo te haya sido útil y te invitamos a leer nuestros artículos anteriores sobre planes de ejecución y caché de planes.
