Plan Caching I - Adhoc Query Caching (SQL Server 2008)

SQL server frequently reuse plan that have already generated and saved in Plan cache rather than go for new plan, but sometime this behavior is not suitable, especially in case of Adhoc query. Every time you run an adhoc query, its plan gets generated and stored in Procedure Cache, whether it will used or not in future.

Just imagine how many Adhoc query you are firing in a day. And if for every query plans get cached then how much memory your Adhoc query consumes.

To overcome from this, SQL Server 2008 comes up with 'optimize for ad hoc workloads', here we will look this feature by doing a small practical.

When Optimize for ad hoc workloads is 0

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

Lets first set the 'optimize for ad hoc workloads' to 0 and check the output of caching.

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.

Here we are freeing the Procedure cache and Buffer, so that we will get only our plan cached in the memory.

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 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         Compiled     PlanAdhoc select * from emp where ename = 'Sneha'

It is clear from the output that when we fire this query first, the plan gets generated and stored in Cache. But if we never use this query again, the plan which gets stored in cache will never be used, so its just a waste of cache memory. Even if the same query is written in lower case or with any cosmetic changed manner, the plan which gets saved will never be used. So it’s just wastage of cache memory.

When Optimize for ad hoc workloads is 1

With SQL Server 2008 we can set “Show advanced Options” to 1 for optimization purpose of Adhoc Queries.

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.

Lets again free the Procedure cache and Buffer, so that we will get only our plan cached in the memory.

dbcc FREEPROCCACHE

DBCC DROPCLEANBUFFERS

Go

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

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 unlike when we have optimize for ad hoc workloads set to 0.

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

select * from emp where ename = 'Sneha'

go 3

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

Beginning execution loop

emp_id      ename      dept_id

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

4           Sneha      1

(1 row(s) affected)

emp_id      ename      dept_id

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

4           Sneha      1

(1 row(s) affected)

emp_id      ename      dept_id

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

4           Sneha      1

(1 row(s) affected)

Batch execution completed 3 times.

usecounts cacheobjtype objtype   text

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

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

Now here we can see the Complied plan get strored in cache and being reference/used 3 times and the CompiledPlanStub is out of the game.

Conclusion : with optimize for ad hoc workloads set to 1, we saw, first time when you run adhoc query, the plan didn’t get stored in cache, if there a call later to the same query the Complied Plan Stub is replaced by actual plan. Unlike optimize for ad hoc workloads set to 1, even if you run query first time, its plan gets stored in cache whether it will be referenced in future or not.

He fluffed up his belt, tossed up trouser on the floor, lay down on bunk bed, toggle up his laptop, keyed in SQL server… he is a programmer  

Post Reference: Vikram Aristocratic Elfin Share