Optimize for ad hoc workloads

Optimize for ad hoc workloads

Enable it through sp_reconfigure –advance options set to 1, if it satisfy the below condition

1.      If the ad-hoc compile plan used more than 2 GB’s it should be considerable.

SELECT objtype AS [CacheType]

        , count_big(*) AS [Total Plans]

        , sum(cast(size_in_bytes as decimal(18,2)))/1024/1024 AS [Total MBs]

        , avg(usecounts) AS [Avg Use Count]

        , sum(cast ((CASE WHEN usecounts = 1 THEN size_in_bytes ELSE 0 END) as decimal(18,2)))/1024/1024 AS [Total MBs - USE Count 1]

        , sum(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END) AS [Total Plans - USE Count 1]

FROM sys.dm_exec_cached_plans

GROUP BY objtype

ORDER BY [Total MBs - USE Count 1] DESC

Benefits:

Once it is turned on, ad-hoc will not create compile plan for the first time execution but if it’s executed as a batch by the same query, compiled plan stub will store the query plan but the space is very negligible.

Code to enable the Optimize for ad hoc workloads

sp_CONFIGURE 'show advanced options',1
RECONFIGURE
GO

sp_CONFIGURE ‘optimize for ad hoc workloads’,1
RECONFIGURE
GO