Grant Fritchey (1/23/2013)
SQLSACT (1/23/2013)
Grant Fritchey (1/23/2013)
There's a pretty focused set of requirements for forced parameterization to work. It's a lot more expanded than simple parameterization, but it does not include ALL ad hoc queries. Microsoft has the requirements documented in the Books Online.Thanks
I just wanted to confirm that with Forced Parameterization - We will deal with plan "shells" ?
Thanks
Plan shells? Not sure what you mean. If you mean plan stubs, then no, that's not from Forced Parameterization. It's from Optimize for Ad Hoc. It's a different setting entirely.
Thanks
No, not stubs
By Shells, I mean this: These ad-hoc entries are shell queries, cached just to make the parameterised form of the query easier to find. All the execution plan contains for these shell queries is a pointer to the plan for the parameterised version of the query.
- Taken from http://sqlinthewild.co.za/index.php/2010/08/31/come-and-gone/
Under Simple Parameterization, if a Query qualifies for it, SQL caches a Parameterized plan with an objtype of "Prepared", any subsequent queries that make use of that plan is also cached, as "Non Parameterized Shell Queries" with an objtype of "AdHoc"
I was noticing the same thing happening with Forced Parameterization.