Optimize for adhoc workloads – Server Configuration Option – How it works

In this article I want to demonstrate with an example how this feature works when configured. let us begin by first understanding what is “Optimize for adhoc workloads” .

This feature helps improve the plan cache’s efficiency to handle workloads that contain many single use of ad hoc batches. When set to 1, the engine keeps a small compiled plan stub in the plan cache when an ad hoc  batch is compiled for the first time, instead of the full compiled plan. This helps in releasing memory pressure by not allowing the plan cache to become filled with compiled plans that will not be used again.

This compiled plan stub enables the SQL engine to decide that an ad hoc batch has been compiled previously but has only stored a compiled plan stub. So when this batch is compiled or executed again, the SQL engine compiles the batch, removes the compiled plan stub from the plan cache, and adds the full compiled plan to the plan cache. This will only affect new plans. Plans that are already in the plan cache are unaffected.

let us understand this with an example
Step 1: Clear the Buffer cache and plan cache

Step 2: Run an Ad hoc query
USE AdventureWorks2012
SELECT * FROM Production.Document WHERE DocumentLevel = 2
WL1Step 3: Find if the 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 Production.Document%’
ORDER BY usecounts DESC;
WL2Step 4: Enable the “Optimize for ad hoc workloads”
SP_CONFIGURE ‘show advanced options’,1
SP_CONFIGURE ‘optimize for ad hoc workloads’,1

Step 5: Repeat Step 1
Step 6: Repeat Step 2
Step 7: Repeat Step 3
You will find that the query is not cached
WL3Step 8: Lets clear the cache and buffers again. Repeat step 1
Step 9: We need to run the ad hoc more than once now. Execute the below
SELECT * FROM Production.Document WHERE DocumentLevel = 2
GO 3
WL4Step 10: Now lets see if the query is cached
Step 11: Repeat step 3.
WL6 Yes we do see that when the same batch has multiple executions, the query is indeed cached.

To summarize the utility of this feature we can say that it helps SQL Server memory by not allowing to store Ad hoc query plans which are for single use only.


Tagged: , , , , , ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: