New “Optimize For Ad hoc Workloads” Option in SQL Server 2008 RC0

There is a very useful new sp_configure option in SQL Server 2008 RC0 called “Optimize for Ad hoc workloads”. This is how Books Online (BOL) describes it:

The optimize for ad hoc workloads option is used to improve the efficiency of the plan cache for workloads that contain many single use ad hoc batches. When this option is set to 1, the Database Engine stores a small compiled plan stub in the plan cache when a batch is compiled for the first time, instead of the full compiled plan. This helps to relieve memory pressure by not allowing the plan cache to become filled with compiled plans that are not reused.

The compiled plan stub allows the Database Engine to recognize that this ad hoc batch has been compiled before but has only stored a compiled plan stub, so when this batch is invoked (compiled or executed) again, the Database Engine compiles the batch, removes the compiled plan stub from the plan cache, and adds the full compiled plan to the plan cache.

Setting the optimize for ad hoc workloads to 1 affects only new plans; plans that are already in the plan cache are unaffected.

This avoids having compiled ad hoc plans taking up space for the full plan in the cache, which reduces memory pressure from the cache being bloated with plans that are not likely to be reused. Very often, the biggest culprits are plans added by the SQL Server Agent as it runs jobs and collects metrics. You can see if you are having problems with this by running the DMV query below:

 

-- Find the ad-hoc queries that are bloating the plan cache
SELECT TOP(100) [text], size_in_bytes 
FROM sys.dm_Exec_cached_plans 
CROSS APPLY sys.dm_exec_sql_text(plan_handle) 
WHERE cacheobjtype = 'Compiled Plan' 
AND objtype = 'Adhoc' AND usecounts = 1
ORDER BY size_in_bytes DESC

 

If you want to take advantage of this new option (which has no downside that I can see), run the commands below:

-- Turn on advanced options
EXEC sp_configure 'Show Advanced Options', 1
GO
RECONFIGURE
GO

-- See what the current value is for 'optimize for ad hoc workloads'
EXEC sp_configure

-- Turn on optimize for ad hoc workloads
EXEC sp_configure 'optimize for ad hoc workloads', 1
GO
RECONFIGURE
GO

-- Finally, clear all the plans in the cache (use with caution on a Production Server)
DBCC FREEPROCCACHE

 

Technorati Tags:
This entry was posted in SQL Server 2008. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s