Plan Caching III - Compiled Plan Stub in Adhoc Query caching with _optimize for ad hoc workloads_ set to 1

Read Previous Post:

• Plan Caching I - Adhoc Query Caching (SQL Server 2008)
• Plan Caching II - Careful consideration needed, if you want to use cached Adhoc Query Plan

When you are running Adhoc SQL query with optimize for ad hoc workloads set to 1, the very first time you wont be able to see any compiled Query plan saved in cache instead you will see some Compiled Plan Stud when you query sys.dm_exec_cached_plans. Now we need to check what this Complied Plan stub is.

We will do a small practical to check this Complied Plan Stub, let’s now create our infrastructure to get Compiled Plan Stub in sys.dm_exec_cached_plans.

For this first we need to set “Show advanced Options”.

sp_CONFIGURE 'show advanced options',1

RECONFIGURE

GO

Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install

Now Let’s set the 'optimize for ad hoc workloads' to 1

sp_CONFIGURE 'optimize for ad hoc workloads',1

RECONFIGURE

GO

Configuration option 'optimize for ad hoc workloads' changed from 0 to 1. Run the RECONFIGURE statement to install.

Now once this advanced setting is done, now we need to free the Procedure cache and Buffer, so that we will get only our plan cached in the memory when we execute our set of query.

dbcc FREEPROCCACHE

DBCC DROPCLEANBUFFERS

Go

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Now since our cache memory is free, let’s fire a SQL query and check the cache using DMV sys.dm_exec_cached_plans.

Now we will run the below query and will check how plan is getting cached up in memory.

select * from emp where ename = 'Sneha'

go

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 emp where ename = ''Sneha''%'

order by usecounts desc

go

emp_id      ename      dept_id

----------- ---------- -----------

4           Sneha      1

(1 row(s) affected)

usecounts cacheobjtype     objtype text

--------- ---------------- ------- -------------------- ----------------

1         CompiledPlanStub Adhoc   select * from emp where ename='Sneha'

So from the output we can see there is no compiled plan which gets stored in cache even after running the select query. What we can see from the output there is something Compiled Plan Stub gets into memory but not the actual plan

Now without clearing the cache and buffer lets run the same query and check the dm_exec_cached_plans output.

select * from emp where ename = 'Sneha'

go

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 emp where ename = ''Sneha''%'

order by usecounts desc

go

usecounts cacheobjtype objtype   text

--------- ------------ -------------------------------------------------

1         Compiled     PlanAdhoc select * from emp where ename = 'Sneha'

Now here we can see the Complied plan get strored in cache and  the CompiledPlanStub is out of the game it is now being replaced by Compiled Plan.  

Complied Plan Stub is not a part of any query execution plan, it is just a kind of place holder to keep track of whether a particular query has been executed proviously. Once a query similar to this gets compiled up, the stub is replaced with actual query plan.

When a query that generated a compiled plan stub is recomplied, th stub is replaced with full complied plan. As we can see when same query gets compiled the Stub is replaced and Complied plan get stored on cache.  

and the knife plunged deeper into his heart, when a programmer was told to document his logic then sit for code…L

Post Reference: Vikram Aristocratic Elfin Share