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