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
- Plan Caching IV - Simple Parameterization in Adhoc Query Processing
In parameterization
of Adhoc query (last
article) we saw the a on the basis of constant it create parameterizated
cached plan and store it in cache memory, but SQL Server makes its decision of
the datatype of constant for example if you run this query after clearing cache
and buffer
select * from emp where emp_id = 1
go
Then executing this sys.dm_exec_cached_plans
query
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
-------- ------------- --------
--------------------------------------------
1 Compiled Plan Prepared (@1
tinyint)SELECT * FROM emp WHERE emp_id=@1
1 Compiled Plan Adhoc select * from emp where emp_id = 1
From the output we can see
that the constant used in query where predict is being treated as tinyint. So the datatype is of the
constant is being selected on fly by SQL Server while making a prepared
Parameterized cache plan.
Now let’s query the same
select with contact value ranging out of tiny data type.
select * from emp where emp_id = 700
go
Then executing this sys.dm_exec_cached_plans
query
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
-------- ------------- --------
--------------------------------------------
1 Compiled Plan Prepared (@1 smallint)SELECT
* FROM emp WHERE emp_id=@1
1 Compiled Plan Adhoc select * from emp where emp_id = 700
Here we can see from the
output that the constant is replaced by parameter and the type for the
parameter is choosed as smallInt unlike
tinyint in previous case.
Now here we will again execute
the first query and will see whether this prepared query plan which is cached
up in memory will get referred.
select * from emp where emp_id = 1
go
lets check sys.dm_exec_cached_plans query now
usecounts cacheobjtype objtype
text
-------- ------------- --------
--------------------------------------------
1 Compiled Plan Prepared (@1 smallint)SELECT
* FROM emp WHERE emp_id=@1
1 Compiled Plan Adhoc select * from emp where emp_id = 700
1 Compiled Plan Prepared (@1 tinyint)SELECT
* FROM emp WHERE emp_id=@1
1 Compiled Plan Adhoc select * from emp where emp_id = 1
So the output of the sys.dm_exec_cached_plans
query shows two adhoc query and two
prepared queries where one prepared query has parameter of type smallint and
another has parameter type as tinyint.
Conclusion: Because of the nature of
SQL Server to automatically choose the data type of constant in the query make
a parameterized cache plan, we are not able to reuse the same plan though the
nature of 2nd query is much similar to the first one.
So what is the option left.. Let’s
check out in the next post same series ;)
Once my MBA friend pulled a knife on
me. I asked her, the knife has butter on it, please don’t disturb, can’t u see I
m writing code!!!… Why she is always angry with me…???
Post Reference: Vikram Aristocratic Elfin Share
No comments:
Post a Comment