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

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

Plan Caching II - Careful consideration needed, if you want to use cached Adhoc Query Plan

Read Previous Post: Plan Caching I - Adhoc Query Caching (SQL Server 2008)

Whenever you fire any adhoc query, SQL server cache the plan in memory for reuse in future reference but to use the cached plan of Adhoc Query, one need to take special consideration while writing the query, any change in the query statement will generated new plan instead of making use of cached plan, statement to statement, word to word, space to space and case to case has to match with the earlier fired query, if you want to reuse the cached plan of query which was executed earlier.

 

Lets do a small practical to know this better. For our practical purpose we have set the database advanced configuration property 'optimize for ad hoc workloads' to 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

Setting 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.

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 platform is ready to execute our explanation, lets fire the below statement and check sys.dm_exec_cached_plans for cached plan.

Test 1

Here we are executing the same select statement twice with the help of GO 2 and then the same query we are executing with a little modification where we changed Case of “select” to “SELECT”  

select * from emp where ename = 'Sneha'

go 2

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

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

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

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

From the output it is clear that we have two plan saved one which is used 2 times where the “select” keyword is in lower case and other where “SELECT” keyword is in Uppercase.

Test 2

Now we will fire the same select statement without any change and will see what dm_exec_cached_plans produce as a result. Just free the cache and buffer before executing our query.

dbcc FREEPROCCACHE

DBCC DROPCLEANBUFFERS

Go

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

select * from emp where ename = 'Sneha'

go

select * from emp where ename = 'Sneha'

go

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

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

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

Here we saw, there is only one plan will gets generated and stored in cache and is reused by further execution of same query.

Test 3

Now again we are executing the same set of queries, just we add a small comment after 2^nd query, let’s see the result 

select * from emp where ename = 'Sneha'

go

select * from emp where ename = 'Sneha'   --2nd

go

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

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

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

1         Compiled     PlanAdhoc select * from emp where ename = 'Sneha' –-2nd

From the output it is evident that if you are putting a small comment to the query which is completely same in all respect to the previously executed query, you will get a new plan.

Conclusion : A single cosmotic modification in the query will produde new plan n wont use the existing plan stored in cache memory. So be aware while writing adhoc queries.

Standing at its brink, like a swimmer who geared up; code bonfire.

Post Reference: Vikram Aristocratic Elfin Share

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