optimize for ad hoc workloads

---

Ya tiene mucho tiempo que no me metía a publicar alguna entrada en mi blog. Hay muchas razones para explicarlo, pero intentaré tener ventanas de tiempo que me permitan seguir creando más entradas y compartir un poco de lo que he podido aprender a lo largo de estos años.

Durante los proyectos de consultoría en SQL Server en los que he estado trabajando de un tiempo para acá, me he encontrado algo muy interesante, que la memoria de SQL está sumamente ocupada almacenando planes de ejecución.

Antes de abordar el tema de este blog de forma directa, me gustaría platicar un poco de los planes de ejecución para que se entienda de mejor manera el impacto que tendrá la opción optimize for ad hoc workloads de SQL Server.

Cuando mandas a ejecutar un query, SQL server realiza una revisión sintáctica y semántica del mismo, una vez que ha pasado la validación procede a calcular un plan de ejecución que resulte óptimo para el query en cuestión.    Para este cálculo toma en cuenta estadísticas, índices, llaves, cantidad de filas, tipos de dato, etc.

El proceso del cálculo de plan de ejecución es de lo más pesado y costoso para SQL Server, realmente mucha parte de la magia de SQL se encuentra ahí.    Debido a este costo SQL Server guarda en memoria el plan de ejecución para que pueda ser reutilizado por un query posterior, es decir que se ahorrará el proceso de cálculo y simplemente se avocará a la ejecución del mismo, optimizando el uso de recursos y mejorando los tiempos de respuesta.

Hasta aquí todo pinta re bien, el problema es cuando nuestra memoria se llena de planes de ejecución de uso único, es decir que el plan de ejecución únicamente ha sido utilizado una vez y nunca volvió a ser reutilizado por otro query.

Para ejemplificar esto vamos a hacer un ejercicio sobre la base de datos AdventureWorks2012 (pueden elegir otra versión de AdventureWorks y no habrá mayor problema)

Es indispensable que no realices este ejercicio en un servidor productivo.

Lo primero será vaciar la memoria de planes de ejecución y el buffer de datos para que tengamos el espacio en blanco:

DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

Después nos conectaremos a la base de datos AdventureWorks2012 y ejecutaremos primero este query:

SELECT ProductID, ListPrice, Color FROM Production.Product WHERE ProductID = 321

Y después este otro query:

SELECT ProductID, ListPrice, Color FROM Production.Product WHERE ProductID = 328

Recordemos que vaciamos la memoria de planes de ejecución y por lo tanto SQL Server tuvo que calcular el mejor plan de ejecución (de los que encontró)    Vamos a revisar la memoria de nuestros planes de ejecución para ver qué tiene:

SELECT CP.usecounts, CP.size_in_bytes, CP.cacheobjtype, CP.objtype, QP.query_plan
FROM sys.dm_exec_cached_plans CP
CROSS APPLY sys.dm_exec_query_plan(CP.plan_handle) QP


Nos deberá dar un resultado como el que muestro en la siguiente imagen:

Podemos ver que tenemos tres planes de ejecución que han sido ocupados una sola vez.    Abre cada uno de los planes de ejecución y notarás que:

• Uno corresponde al query que buscó al producto 321.
• Uno corresponde al query que buscó al producto 328
• Uno corresponde al query que obtuvo la información de los planes de ejecución.

Si ejecutamos ahora el siguiente query:

SELECT ProductID, ListPrice, Color FROM Production.Product WHERE ProductID = 329

Y volvemos a obtener el contenido del caché de planes de ejecución, ahora veremos que se incrementó el usecounts del plan de ejecución que obtiene el contenido del caché de planes de ejecución y que tenemos un nuevo plan almacenado resultado de la búsqueda del producto 329.

¿Estamos de acuerdo que pudo haberse utilizado el mismo plan de ejecución para buscar los productos 321, 328 y 329?, finalmente la búsqueda se hizo en base al CLUSTERED INDEX.    Peor aún, si nos pusiéramos a buscar muchos productos por su identificador acabaríamos con muchos planes de ejecución idénticos en la memoria, desperdiciando recursos que podríamos ocupar en cosas más valiosas.

Aquí es donde entra la configuración del optimize for ad hoc workloads.    Esta opción de SQL Server busca parametrizar planes de ejecución para que puedan ser reutilizados varias veces y no terminar con una memoria atascada de basura.

Para probar esto, vamos a habilitar la opción optimize for ad hoc workloads de la siguiente manera:

sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
sp_configure 'optimize for ad hoc workloads', 1
GO
RECONFIGURE

Ahora vamos a limpiar la memoria:

DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS


Acto seguido ejecutaremos las cosas en el mismo orden que lo hicimos antes de habilitar la opción optimize for ad hoc workloads:

SELECT ProductID, ListPrice, Color FROM Production.Product WHERE ProductID = 321

Después:

SELECT ProductID, ListPrice, Color FROM Production.Product WHERE ProductID = 328

Después:

SELECT CP.usecounts, CP.size_in_bytes, CP.cacheobjtype, CP.objtype, QP.query_plan
FROM sys.dm_exec_cached_plans CP
CROSS APPLY sys.dm_exec_query_plan(CP.plan_handle) QP

Después:

SELECT ProductID, ListPrice, Color FROM Production.Product WHERE ProductID = 329

Y finalmente:

SELECT CP.usecounts, CP.size_in_bytes, CP.cacheobjtype, CP.objtype, QP.query_plan
FROM sys.dm_exec_cached_plans CP
CROSS APPLY sys.dm_exec_query_plan(CP.plan_handle) QP

Si revisamos y comparamos la memoria utilizada del antes y el después de habilitar la opción optimize for ad hoc workloads notaremos un ahorro ¡del 39.68%!

Esto resulta especialmente útil en entornos donde SQL Server recibe solicitudes enviadas desde sistemas que utilizan el .NET Entity Framework.

¡Aguas!, no es la cura para todos los males, esto nos obligará a realizar las mejores prácticas y crear una capa de acceso a datos mediante vistas, procedimientos almacenados y funciones para obtener la información de nuestras bases de datos.    Aunque, siendo sincero, la opción de la capa de datos la considero obligatoria independientemente del estado de la opción optimize for ad hoc workloads.

De hecho es considerada una buena práctica el habilitar esta opción en toda instalación de SQL Server.

Espero te haya resultado de utilidad esta entrada, ¡saludos!

---