Read Previous Post:
Though you need to be careful while setting this “parameterization forced” since setting this option “on” for the database, you are letting SQL Server assume and treat all constant as a same parameter, which may give poor performance. Alternatives are available, which I will soon post as an article.
Post Reference: Vikram Aristocratic Elfin Share
- 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
- Plan Caching V - Disadvantage of Simple Parameterization
In the last article we saw
that the datatype of constant is selected on fly depending upon the constant
value by SQL Server, due to which the cached Prepared Parameterized plan is not
been used by a similar query with constant little different.
Just check this, below example.
dbcc
FREEPROCCACHE
DBCC
DROPCLEANBUFFERS
Go
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
tinyint)SELECT * FROM emp WHERE emp_id=@1
1 Compiled Plan Adhoc select * from emp where emp_id = 1
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 it has not used the Prepared plan which gets generated when we executed
query with predicate constant value as 1 and generate a new prepared plan and
cached in memory; this time the constant is replaced by parameter and the type
for the parameter is choosed as smallInt
unlike tinyint in previous case.
So we
get two prepared queries plan with two different parameter data type. So SQL Server
is not making use of existing Parameterized cached plan.
The
only way to force SQL Server to use the same data type for both the queries is to enable
PARAMETERIZED FORCED for the database.
dbcc
FREEPROCCACHE
DBCC
DROPCLEANBUFFERS
Go
alter database
sparsh set parameterization
forced;
go
Here
we have set PARAMETERIZATION FORCED enabled. Once this is done SQL Server
treats every kind constants as just a set of parameters.
Now
since we have enabled PARAMETERIZATION FORCED. Lets run both the select query
and query the dm_exec_cached_plans to check the output.
select * from emp where emp_id = 1
go
select * from emp where emp_id = 700
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
-------- ------------- --------
--------------------------------------------
2 Compiled Plan Prepared (@0 int)SELECT *
FROM emp WHERE emp_id=@0
1 Compiled Plan Adhoc select * from emp where emp_id = 1
1 Compiled Plan Adhoc select * from emp where emp_id = 700
Here
we can see that no new Prepared plan gets generated, instead the same prepared
plan is referred by the second query with emp_id = 700. And the data type chosen
by SQL Server for constant is INT which satisfy both the constant.
Though you need to be careful while setting this “parameterization forced” since setting this option “on” for the database, you are letting SQL Server assume and treat all constant as a same parameter, which may give poor performance. Alternatives are available, which I will soon post as an article.
If my girl friend judge my love to
code is like putting them in a strait jacket and kicking them down a flight,
then yes.. I have deep love to my code K
Post Reference: Vikram Aristocratic Elfin Share
No comments:
Post a Comment