When running batches in SQL Server, each batch creates an execution plan that is stored in the system for re-use. However, many of these plans are only used once and are never re-used again. This results in wasted memory and resources.

SQL Server 2008 introduced a feature called “Optimizing Ad Hoc Workloads” to address this issue. By enabling this feature, SQL Server can optimize the storage and execution of ad hoc queries that are only used once.

Let’s take a look at how SQL Server behaves without optimizing ad hoc workloads. Run the following script to see the effect:

/* Test 0 */
/* Clean Cache and Buffers */
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

USE AdventureWorks

/* Run Adhoc Query First Time */
SELECT * FROM HumanResources.Shift

/* Check if Adhoc query is cached */
SELECT usecounts, cacheobjtype, objtype, TEXT
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE usecounts > 0
AND TEXT LIKE '%SELECT * FROM HumanResources.Shift%'
ORDER BY usecounts DESC;

After running the script, you will see that the query plan for the ad hoc query is cached in memory. However, if we never run this query again in the future or if we only ran it as part of building a longer query, the cached plan for this query is just a waste of memory.

To enable the option of optimizing ad hoc workloads, use the following commands:

sp_CONFIGURE 'show advanced options', 1
RECONFIGURE

sp_CONFIGURE 'optimize for ad hoc workloads', 1
RECONFIGURE

Now, let’s run the code for Test 1, which is almost the same as Test 0, but with the cache and buffer cleaned:

/* Test 1 */
/* Clean Cache and Buffers */
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

USE AdventureWorks

/* Run Adhoc Query First Time */
SELECT * FROM HumanResources.Shift

/* Check if Adhoc query is cached */
SELECT usecounts, cacheobjtype, objtype, TEXT
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE usecounts > 0
AND TEXT LIKE 'SELECT * FROM HumanResources.Shift%'
ORDER BY usecounts DESC;

After running Test 1, you will notice that the ad hoc query is not cached anymore. This is because we have enabled the option to optimize ad hoc workloads. The query plan is only stored in the cache if the batch is run more than once.

Let’s run Test 2 to see this behavior:

/* Test 2 */
/* Clean Cache and Buffers */
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

/* Run Adhoc Query two times */
SELECT * FROM HumanResources.Shift
GO 5

/* Check if Adhoc query is cached */
SELECT usecounts, cacheobjtype, objtype, TEXT
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE usecounts > 0
AND TEXT LIKE '%SELECT * FROM HumanResources.Shift%'
ORDER BY usecounts DESC;

As you can see from the results, when the batch is run multiple times, the execution plan is cached. This behavior is consistent whether or not the option to optimize ad hoc workloads is enabled.

This feature is particularly important for environments with millions of ad hoc queries running every day. By not storing the compiled plans for single-use queries, memory pressure is relieved and performance is improved.