SQL Server Performance Forum – Threads Archive

Uncompiled sprocs and cached execution plans?

This question came up in an earlier thread http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=14091): If an uncompiled procedure runs a couple of queries, then would SQL Server look at separate execution plans available in cache for the individual statements?

---

i am inclined to presume not,
1. i just don’t think that optimizing the optimization process was a priority, except for the cap on excessive cost.
2. assume that the sub-queries are SQL, and not another sp.
S2K will only auto paramterized single table single SARG sql,
meaning that for most queries, the plan will be reused if the same SARG values are specified.
the way a stored procedure plan works is on the compile,
the input parameters are used to lookup distribution statistics.
so if these are different, then what ever in cache is not even applicable.

---

Are you saying a plan can only be reused if all parameters are identical? Any change in the parameters, and the sproc will be recompiled?

---

no, thats for SQL, which is why you absolutely must stored procs, with some parameterized sql. the stored proc compiles using the distribution values passed in on the compile,
every following will use that plan until the next recompile/compile so i think this is dangerous if you have skewed distribution and no guarantee of what parameters generated the compile.
i actually like to have a main proc that calls procs with different names, but identical code.
each proc only gets calls with parameters having a certain distribution example. 2 SARG.
if SARG 1 has high count, then proc 1
if SARG 1 is low and SARG 2 is high, then proc 2 you don’t need to cover every combination, only the only the ones that lead to plans with significant differences in true cost

---

]]>