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

 

image

 

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.

image

 

Ahora ejecutemos nuevamente el TSQL anterior y vemos que ahora se muestra un Compiled Sub Plan y no muestra el plan compilado en cache

 

image

 

Si ejecutamos el TSQL más de una vez, el plan completo quedará en caché, tal y como se muestra a continuación

 

image

 

 

Saludos,

 

Eduardo Castro Martinez

http://ecastrom.blogspot.com

http://comunidadwindows.org

twitter: edocastro