Plan Caching IV - Simple Parameterization in Adhoc Query Processing

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
• Plan Caching III - Compiled Plan Stub in Adhoc Query caching with _optimize for ad hoc workloads_ set to 1

Some time SQL Server consider some constant of Query as a Parameter, and when this happens, any query subsequently executed after that having a kind of similar template will use the same plan. We will check this by taking an example. Will try to execute sys.dm_exec_cached_plans to know the exact happening.

Now let’s set the 'optimize for ad hoc workloads' to 0

sp_CONFIGURE 'optimize for ad hoc workloads',0

RECONFIGURE

GO

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

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.

select * from emp where emp_id = 1

go

select * from emp where emp_id = 2

go

select * from emp where emp_id = 3

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] not like '%dm_exec_cached_plans%' and [text] not like  '%dm_exec_sql_text%'

order by usecounts desc

go

usecounts cacheobjtype  objtype  text

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

3         Compiled Plan Prepared (@1 tinyint)SELECT * FROM emp WHERE emp_id=@1

1         Compiled Plan Adhoc    select * from emp where emp_id = 3

1         Compiled Plan Adhoc    select * from emp where emp_id = 2

1         Compiled Plan Adhoc    select * from emp where emp_id = 1

Here we can see that the plan is first compiled and cached in memory taking constant as parameter and when next time a query of similar kind came for execution, this plan which is cached in memory gets referenced by the executer.

And the rest three compiled plan is just stored to as an act of finding parameterized version of the query, if exact same query with same constant come for execution.

Code is a kind of color in programmers being, to aid & strikes the pond of contentment… 

Post Reference: Vikram Aristocratic Elfin Share