On Feb 21, 4:48 pm, "rob" <robin.dinda...@gmail.com> wrote:
> Hi There,
> I have a query that has drastically different execution plans when run
> as an ad-hoc query vs. a stored procedure, is this normal? The reason
> I ask is because it causes the stored procedure to run extremely bad
> when one parameter changes.
>
> Here are some details about the query...
> - it is joining 4 tables (1 table has 400,000+ rows and the other 3
> have approximately 40,000 rows)
> - when running with parameter = x in the stored procedure, profiler
> says there are between 300 and 3,300,000 reads depending on the value
> of x
> - when running with parameter = x as an ad-hoc query, profiler says
> there are between 300 & 500 reads depending on the value of
>
> My guess is that when the ad-hoc query is run, SQL comes up with an
> optimal execution plan based on the provided parameter. However, when
> run as a stored procedure there is only one execution plan and it only
> optimal for one value of the parameter. Does this make sense? Or am
> I just trying justify this crazy behaviour?
>
> Any help would be greatly appreciated.

On Feb 21, 6:48 pm, "rob" <robin.dinda...@gmail.com> wrote:
> Hi There,
> I have a query that has drastically different execution plans when run
> as an ad-hoc query vs. a stored procedure, is this normal? The reason
> I ask is because it causes the stored procedure to run extremely bad
> when one parameter changes.
>
> Here are some details about the query...
> - it is joining 4 tables (1 table has 400,000+ rows and the other 3
> have approximately 40,000 rows)
> - when running with parameter = x in the stored procedure, profiler
> says there are between 300 and 3,300,000 reads depending on the value
> of x
> - when running with parameter = x as an ad-hoc query, profiler says
> there are between 300 & 500 reads depending on the value of
>
> My guess is that when the ad-hoc query is run, SQL comes up with an
> optimal execution plan based on the provided parameter. However, when
> run as a stored procedure there is only one execution plan and it only
> optimal for one value of the parameter. Does this make sense? Or am
> I just trying justify this crazy behaviour?
>
> Any help would be greatly appreciated.