The Optimise for Adhoc Workloads is a setting that I had never given a lot of thought to.  I credit my knowledge of it entirely to new MVP Martin Catherall, who casually dropped into conversation that it is an option he usually configures by default.  I asked a few other people around this and it seems that the option is either completely unknown or considered to be a standard deployment.  So I’ve done a bit of reading and a bit of testing, and now it’s one I consider pretty much a default deployment as well.

Option Name:  optimize for adhoc workloads

Usage:  sp_configure ‘optimize for adhoc workloads’, 1

Default: 0

Maximum: 1

Requires Restart:  No

What it does:  A certain portion of your SQL buffer pool is used to store cached execution plans to be reused by SQL Server if that query comes along again.  This efficiently saves SQL Server to go through the process of generating an execution plan for the same query over and over and over again(Unless your developers are in love with OPTION RECOMPILE of course).  This option is to protect against your cache being filled up with execution plans that only ever get used once.  Effectively it stores a ‘stub’ of that execution plan and then only stores the full plan if it sees it being executed again, which saves a lot of space.

When should I use it:  Almost always!  I’ve put the question to a lot of different people at various SQL Saturday expert panels and user group presentations.  The answer is pretty much always that there is really no downside in enabling this option.  If you have a server with a lot of adhoc querying occurring it will save space in the buffer pool than can be used for data pages = faster execution.  If you have a server with only a couple of queries executing multiple times there is a small impact in that the queries each have to run twice before the full plan is cached after a restart.  But in that instance your plan cache is likely to be smaller anyway due to the limited number of plans in it.

What else should you know:  To see if you are likely to get any benefit from the setting – check what’s happening on your server right now(disclaimer:  You’ll want to have had a little while since the last restart before running this script as the DMV’s it accesses are reset on service restart.)

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
go

(Code Courtesy of Kimberly Tripp’s blog post)

That code will tell you how many adhoc 1 use plans you have in the cache, together with how much space those plans are using right now.  You might be surprised!