Plan Caching VI - Forced Parameterization in Adhoc Queries

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
• 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