Simple parameterization

I was checking my cache plan , and founded that there was a database with more then 44k+ plans , that are only one time used.

Digging up, I discovered a query and asked my self , way it wasn’t parameterized.

Here is a sample:

select *
from sales.SalesOrderHeader as soh
where soh.SalesOrderID = 43675

 

select TOP(1) *
from sales.SalesOrderHeader as soh
where soh.SalesOrderID = 43675

 

Here is the execution plan(part of it)

 

and xml representation:

 

Simple parameterization can happens , in the following situation :
– must be a trivial plan . if it’s not trivial, no simple parameterization
in xml plan is : StatementOptmLevel=”TRIVIAL”

– the query must not have JOIN, IN, TOP,GROUP BY, HAVING, Distinct , Subqueries …

 

Conclusion:

QO decide to parameterize a query when it consider it safe to param , in a trivial plan , and not have : joins, In, Top, subqueries….

 

S