Optimizing for Ad hoc Workloads
One of the nifty features available in SQL Server 2008 is the "Optimize for Ad hoc Workloads" option, which is at the instance level. If your workload involves a high number of ad hoc queries that are used only once, your plan cache (also known as the "procedure cache") may benefit from enabling this feature. (There are, of course, other solutions to this issue as well, a primary one being to use less ad-hoc SQL and more stored procedures, but that may not always be possible.)
What it does
SQL Server has an upper limit on the size of its plan cache, which is dependent on the amount of memory installed. In systems with high ad hoc workloads, a disproportionate amount of this cache may be going towards query plans that are only executed once. Enabling "Optimize for Ad hoc Workloads" prohibits the caching of plans that are only used once, freeing up the cache for other objects that might be utilized more often.
If you're curious, in 64-bit SQL Server 2008 (and also 2005 SP2 and later) the maximum size for the plan cache is calculated as follows:
(75% of server memory from 0-4 GB) + (10% of server memory from 4-64 GB) + (5% of server memory beyond 64 GB).
So an x64 server with 32GB of RAM would have a maximum plan cache size of (0.75*4) + (0.1*28) = 5.8GB.
32-bit servers would have a maximum 3GB plan cache size, as only 4GB of memory is considered. Any additional memory allocated via AWE is accessible only to the buffer cache and not the plan cache.
How It Works
At a very high level, the SQL Query Optimizer takes in a batch for execution and tries to locate an appropriate query plan in the plan cache. If it's unable to find one, it compiles a plan and stores it in cache so that the next time it's called, the cost of compiling such a plan is not incurred. The downside to this is that if the batch (and hence the plan) is only used once, that plan will take up space in memory that could be better allocated towards something else.
When "Optimize for Ad hoc Workloads" is enabled, an extra step is added to the above process. The first time a plan is created for a batch, it isn't stored in the cache. Instead, a hash of the plan is stored, which takes up much less space. If a plan is used that already has a hash stored, then the full plan is added to the cache since it has now been used twice and is likely to be called again in the future.
What To Look For
The dm.exec_cached_plans DMV allows us to peek into the plan cache and see what's taking up space. A few simple queries with it can provide a bunch of information. Try running these three statements together:
1SELECT SUM(cast(size_in_bytes AS BIGINT))/1024/1024 AS 'Size (MB)'
2FROM sys.dm_exec_cached_plans;
3
4SELECT objtype AS 'Type',
5COUNT(*) AS '# Plans',
6SUM(CAST(size_in_bytes AS BIGINT))/1024/1024 AS 'Size (MB)',
7AVG(usecounts) AS 'Avg uses'
8FROM sys.dm_exec_cached_plans
9GROUP BY objtype;
10
11SELECT SUM(cast(size_in_bytes AS BIGINT))/1024/1024 AS 'Size (MB)'
12FROM sys.dm_exec_cached_plans
13WHERE objtype = 'Adhoc' AND usecounts = 1;The first result set will tell you the total size of the procedure cache. The second one breaks that number down by object type. The third tells the size of the procedure cache being taken up by Ad hoc query plans that have only been used once. Here's some results I recently retrieved:
As you can see, 3125 MB of the 3330 MB plan cache is going towards Ad hoc query plans which are used a relatively low number of times compared to other object types. Also 2240 MB of the 3125 MB goes towards plans that have only been used once. This machine is a good candidate for enabling the optimization.
How To Enable It
Enabling this optimization is pretty straightforward, just run the following script. Again, this option is at the instance level, so it will be enabled for all databases on that instance.
1sp_configure 'show advanced options',1;
2RECONFIGURE;
3GO
4
5sp_configure 'optimize for ad hoc workloads',1;
6RECONFIGURE;
7GO
8
9DBCC FREESYSTEMCACHE('SQL Plans');You can also check the "Advanced" section of the Server Properties menu and change it from there.
Either way, you'll want to run
1DBCC FREESYSTEMCACHE('SQL Plans')afterwards, as it will clear the procedure cache for ad hoc queries. "Optimize for Ad hoc Workloads" only applies to plans not already in the cache, so you won't see any changes until you purge those pre-existing entries.