Auto Parameterization in SQL Server

Let us look at how and when SQL Server can parameterize dynamic SQL to reuse the query plan.Its through Parsing and compiling that the query plans get in to memory and then may or may not be reused.

Before executing a batch of SQL statements, SQL Server creates an execution plan (or query plan) for those statements. This is also referred to as compiling the SQL statements. One of the benefits of stored procedures is that SQL Serve will reuse compiled query plans.

Here is the example :
DBCC FREEPROCCACHEGO
set showplan_all on
SELECT *FROM Sales.SalesOrderHeaderWHERE SalesOrderID = 56000GO
select stats.execution_count AS exec_count, p.size_in_bytes as [size], [sql].[text] as [plan_text]from sys.dm_exec_cached_plans pouter apply sys.dm_exec_sql_text (p.plan_handle) sqljoin sys.dm_exec_query_stats stats ON stats.plan_handle = p.plan_handleGO

Using the above mentioned DMV's we can find the number of times this plan has been executed, how big the plan is in bytes and the text of the plan.

Notice that the integer 56000 has been replaced by a variable ([@1])in the text of the query plan. Also notice that the list of parameters (only one parameter in this case) has been inserted at the beginning of the query plan. This is called "Simple Parameterization" (It was called "Auto Parameterization" in SQL Server 2000). If we run it twice we'd see the execution count increase to two but there would still only be a single plan.
SQL Server can also parameterize queries if they aren't identical.

lets check this :

DBCC FREEPROCCACHEGO
SELECT *FROM Sales.SalesOrderHeaderWHERE SalesOrderID = 56000GO
SELECT * FROM Sales.SalesOrderHeader WHERE SalesOrderID = 56001GO
select *from Sales.SalesOrderHeaderwhere SalesOrderID = 56002GO
select stats.execution_count AS exec_count, p.size_in_bytes as [size], [sql].[text] as [plan_text]from sys.dm_exec_cached_plans pouter apply sys.dm_exec_sql_text (p.plan_handle) sqljoin sys.dm_exec_query_stats stats ON stats.plan_handle = p.plan_handleGO

Now let's look at something different.

DBCC FREEPROCCACHEGO
SELECT *FROM Sales.SalesOrderHeaderWHERE SalesOrderID = 56000GO
SELECT * FROM AdventureWorks.Sales.SalesOrderHeader WHERE SalesOrderID = 56001GO
declare @i intset @i = 56004SELECT *FROM Sales.SalesOrderHeaderWHERE SalesOrderID = @iGO
select stats.execution_count AS exec_count, p.size_in_bytes as [size], [sql].[text] as [plan_text]from sys.dm_exec_cached_plans pouter apply sys.dm_exec_sql_text (p.plan_handle) sqljoin sys.dm_exec_query_stats stats ON stats.plan_handle = p.plan_handleGO

So,even slight changes in the text of the plan prevent SQL Server from reusing the query plans.If you want SQL Server to parameterize your SQL statements you have three options: stored procedures, sp_executesql or Forced Parameterization. Stored procedures almost always have a query plan created and reused.
Lets try without sp_executesql first
---------------------------------------------
DBCC FREEPROCCACHEGO
SELECT SUM(LineTotal) AS LineTotalFROM Sales.SalesOrderHeader HJOIN Sales.SalesOrderDetail D ON D.SalesOrderID = H.SalesOrderIDWHERE H.SalesOrderID = 56000GO
SELECT SUM(LineTotal) AS LineTotalFROM Sales.SalesOrderHeader HJOIN Sales.SalesOrderDetail D ON D.SalesOrderID = H.SalesOrderIDWHERE H.SalesOrderID = 56001GO
select stats.execution_count AS exec_count, p.size_in_bytes as [size], LEFT([sql].[text], 80) as [plan_text]from sys.dm_exec_cached_plans pouter apply sys.dm_exec_sql_text (p.plan_handle) sqljoin sys.dm_exec_query_stats stats ON stats.plan_handle = p.plan_handleGO

Now we will use sp_executesql
------------------------------------------
Now we will use sp_executesql .
DBCC FREEPROCCACHEGO
EXEC sp_executesql N'SELECT SUM(LineTotal) AS LineTotalFROM Sales.SalesOrderHeader HJOIN Sales.SalesOrderDetail D ON D.SalesOrderID = H.SalesOrderIDWHERE H.SalesOrderID = @SalesOrderID', N'@SalesOrderID INT', 56000GO
EXEC sp_executesql N'SELECT SUM(LineTotal) AS LineTotalFROM Sales.SalesOrderHeader HJOIN Sales.SalesOrderDetail D ON D.SalesOrderID = H.SalesOrderIDWHERE H.SalesOrderID = @SalesOrderID', N'@SalesOrderID INT', 56005GO
select stats.execution_count AS exec_count, LEFT([sql].[text], 80) as [plan_text]from sys.dm_exec_cached_plans pouter apply sys.dm_exec_sql_text (p.plan_handle) sqljoin sys.dm_exec_query_stats stats ON stats.plan_handle = p.plan_handleGO
See the difference :D
You can set the parameterization at databse level by :
ALTER DATABASE adventureworks set PARAMETERIZATION SIMPLE.

Awaiting your comments :)