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 2nd 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
