A partir de SQL Server 2008 existe una opción llamada optimize for ad hoc workloads (http://msdn.microsoft.com/en-us/library/cc645587.aspx) esta opción permite optimizar el uso de memoria para el almacenamiento de caché de planes de ejecución, en resumen el caso es el siguiente: cada vez que ejecutamos un T-SQL en SQL Server se crea un plan de ejecución que se almacena para su posible reutilización, sin embargo existen muchos planes de ejecución que será utilizados una única vez, por lo tanto almacenar ese plan de ejecución utiliza memoria para un plan que no se va a volver a utilizar. La opción ‘optimize for ad hoc workloads’ configura SQL Server para que solo aquellos planes de ejecución que se utilizan más de una vez sean almacenados en caché.
Para activar dicha opción se debe ejecutar el siguiente T-SQL
sp_CONFIGURE 'show advanced options',1
RECONFIGURE
GO
sp_CONFIGURE ‘optimize for ad hoc workloads’,1
RECONFIGURE
GO
Para probar este comportamiento utilizamos el siguiente script
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
GO
USE AdventureWorks2008R2
GO
/* Ejecutar un TSQL Ad Hoc */
SELECT * FROM Person.Address
GO
/* El siguiente TSQL verifica si el plan esta en cache */
SELECT usecounts, cacheobjtype, objtype, TEXT
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE
TEXT LIKE '%SELECT * FROM Person.Address%'
GO
El resultado default de SQL Server es el siguiente
Podemos ver en el resultado anterior que el plan aunque fue ejecutado una vez, ya está en caché. Ahora configuremos optimize for ad hoc workloads en SQL Server.
Ahora ejecutemos nuevamente el TSQL anterior y vemos que ahora se muestra un Compiled Sub Plan y no muestra el plan compilado en cache
Si ejecutamos el TSQL más de una vez, el plan completo quedará en caché, tal y como se muestra a continuación
Saludos,
Eduardo Castro Martinez
http://ecastrom.blogspot.com
http://comunidadwindows.org
twitter: edocastro
