What is Optimize for ad hoc workloads option in SQL 2008 and how to configure

The optimize for ad hoc workloads option can improve the overall performance of your SQL Server. This option is introduced in SQL 2008 to improve the efficiency of the plan cache. At the moment a query is started SQL Server will first analyze the query to find the best way to execute the query. This is called the execution plan. In the execution plan information is stored about for instance which index to use to retrieve the data. All execution plans are stored in the plan cache. If a new query is executed, SQL server will look if an execution plan is available for this query in the plan cache. If it is available it will be re-used. This will save the generation of the execution plan. For every execution plan SQL server counts how much time it is used.
With next query you can see the content of the plan cache.

SELECT TOP 50 usecounts, cacheobjtype, objtype, TEXT, CAST(size_in_bytes AS BIGINT)/1024 as Size_in_Kb
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)

What you will see is a lot of queries which are executed ones. (Column Usecount = 1). With next query you can retrieve the number of queries and the size in the plan cache which are executed ones:

SELECT COUNT(*), SUM(CAST(size_in_bytes AS BIGINT))/1024 AS Size_in_Kb
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE usecounts = 1

You will see a big number of queries which are executed ones. Now compare these values with the queries which are executed more than ones.

SELECT COUNT(*), SUM(CAST(size_in_bytes AS BIGINT))/1024 AS Size_in_Kb
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE usecounts > 1

Because a lot of queries are executed ones it is not so useful to store the full execution plan in the plan cache. The optimize for ad hoc workloads will store a small compiled plan stub in the plan cache when a query is compiled for the first time, instead of the full compiled plan. The compiled plan stub allows the Database Engine to recognize that this ad hoc query has been compiled before but has only stored a compiled plan stub, so when this query is invoked (compiled or executed) again, the Database Engine compiles the query, removes the compiled plan stub from the plan cache, and adds the full compiled plan to the plan cache. One stub plan takes about 0.25 Kb. A full compiled plan is much bigger. For complex queries is can be more than 100 Kb. This is a factor 400 bigger.
Run next query to get an overview of the different cache objects in the plan cache.

SELECT objtype, cacheobjtype,COUNT(*) AS Number_of_plans, SUM(CAST(size_in_bytes AS BIGINT))/1024/1024 AS size_in_MBs,AVG(usecounts) AS avg_use_count, (SUM(CAST(size_in_bytes AS BIGINT))/1024)/COUNT(*) AS average_size
FROM Sys.dm_exec_cached_plans
GROUP BY Objtype, cacheobjtype
ORDER BY Objtype, cacheobjtype

Save the result set of this query. Now we have the plan cache statistics of your server. Now it's time to enable the optimize for ad hoc workloads option. Enable the  optimize for ad hoc workloads option with next query:

'show advanced options',1
RECONFIGURE
GO
SP_CONFIGURE 'optimize for ad hoc workloads',1
RECONFIGURE
GO

Re run the query to get an overview of the different cache objects in the plan cache after a while. Now you will see the number of Compiled plans will be much lower becaues these will only containts queries which are executed more than ones.  The number of Compiled Plan Stub will still be high, however the average size is very small.
Photo credit: HIRATA Yasuyuki