Forced Parameterization

If large numbers of ad hoc statement to your database are executed then performance of your server may degrade because of excessive recompilation. And such recompilation can not be identified by using below statement:

select top 10

      qs.plan_generation_num,

      qs.execution_count,

      DB_NAME(st.dbid) as DbName,

      st.objectid,

      st.text

from sys.dm_exec_query_stats qs

cross apply sys.dm_exec_sql_text(sql_handle) as st

order by plan_generation_num desc

Because plan_generation_num for such queries is always 1. Below is the presentation of such scenario:

DBCC FREEPROCCACHE

GO

USE AdventureWorksLT

GO

SELECT * FROM dbo.tblPerson WHERE Age = 30

GO

SELECT * FROM dbo.tblPerson WHERE Age = 35

GO

SELECT * FROM dbo.tblPerson WHERE Age = 40

GO

SELECT * FROM dbo.tblPerson WHERE Age = 42

GO

select top 10

      qs.plan_generation_num,

      qs.execution_count,

      st.text

from sys.dm_exec_query_stats qs

cross apply sys.dm_exec_sql_text(sql_handle) as st

order by plan_generation_num desc

The result of last select statement is as following:

plan_generation_num	execution_count	text
1	1	SELECT * FROM dbo.tblPerson WHERE Age = 40
1	1	SELECT * FROM dbo.tblPerson WHERE Age = 35
1	1	SELECT * FROM dbo.tblPerson WHERE Age = 30
1	1	SELECT * FROM dbo.tblPerson WHERE Age = 42

 

 

 

 

 

 

This result shows that every query is compiled separately and execution plans are not reused.

Here if we want that SQL Server should reuse the compilation plan of such queries that are different in few criteria only. We can do that by setting the PARAMETERIZATION option to FORCED in the ALTER DATABASE statement. Now I execute the below statement:

ALTER DATABASE AdventureWorksLT SET PARAMETERIZATION FORCED;

Now I execute the first batch again and the result of last select statement is as following:

plan_generation_num	execution_count	text
1	4	(@0 int)select * from dbo . tblPerson where Age = @0

 

 

Here we see that query is compiled only once and executed for 4 times. Here the query not contains the specific age value but a parameter. At run time this parameter is set to passed value. Here SQL Server automatically converted the ad hoc query to a parameterized query. This is something like what we do in stored procedure.

For more details visit to: http://msdn.microsoft.com/en-us/library/ms175037.aspx