Microsoft SQL Server: Optimize for ad-hoc workloads Explained!

UPDATED: 02 August 2014

SQL Server maintains cache for execution plan. This cache maintained to optimize query performance. Each query has its own execution plan stored in memory. It will lead to memory overhead for SQL Server. It will also be suppression to find exact execution. We need to cache only those queries which executed multiple times.

Optimize for ad-hoc workloads
"optimize for ad hoc workloads" option helps to cache only those execution plan(Query) in memory which executed multiple times. This option comes with two flag 1 [TRUE] and 0 [FALSE]

  • 1 [TRUE] - Turn on the cache optimization. 
  • 0 [FALSE] - Turn off the cache optimization.

Cache Object Type
There are several other types of object for cache but will focus only on two "Compiled Plan" and "Compiled Plan Stub".

  • Compiled Plan: This object holds full execution plan in cache.
  • Compiled Plan Stub: This object holds stub (end point) of execution plan.

Download Microsoft's sample database
I'm using AdventureWorks2008R2 Database for example.
Link: http://msftdbprodsamples.codeplex.com/releases/view/93587.


Queries in Action

First of all lets find how much memory occupied in cache. 
/* Get memory used for execution plan cache */
SELECT SUM(size_in_bytes)/1024.0/1024.0 AS [Total MB used]
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)

To understand the real time example lets turn off "optimize for ad hoc workloads" option and clear all execution plan from cache.
EXECUTE sp_configure 'optimize for ad hoc workloads', 0
RECONFIGURE
GO

DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
GO

Now execute simple select query on database and generate execution plan cache.
SELECT * FROM HumanResources.Employee

To check query execution plan cache execute below query.
SELECT usecounts, size_in_bytes, cacheobjtype, text
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE cacheobjtype != 'Parse Tree'

optimize for ad-hoc workloads Micosoft SQL Server

Explanation
When you disable/turn off "optimize for ad hoc workloads" option, cache will stores whole execution plan regardless query will be used in future or not. This will create memory overhead. Lets see the counter part of this option.


Now clear all execution plan cache and enable/turn on "optimize for ad hoc workloads" option.
EXECUTE sp_configure 'optimize for ad hoc workloads', 1
RECONFIGURE
GO

DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
GO

Execute same query and check the execution plan cache. Execute both the query separately.
SELECT * FROM HumanResources.Employee

SELECT usecounts, size_in_bytes, cacheobjtype, text
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE cacheobjtype != 'Parse Tree'

optimize for ad-hoc workloads Micosoft SQL Server

Explanation
When you enable/turn on "optimize for ad hoc workloads" option, SQL server only store Compiled Plan Stub of query which executed once. If you fire same query again then SQL Server get to know that this query already executed once so it will remove Compiled Plan Stub from memory and replace it with Compiled Plan.

optimize for ad-hoc workloads Micosoft SQL Server


0 comments :