Execution plans differ

I have a stored procedure that is taking too long to execute. In trying to
diagnose the problem and come up with a solution, I executed the query the
stored procedure runs in query analyzer and it ran very fast. I compared th
e
two execution plans and they are vastly different.
to clarify I took the text of the stored procedure and copied it into query
analyzer. The stored procedure takes 2 paramaters. I declared the 2
parameters as variables and set them to the values that were being passed
into the stored procedure.
When executed in the stored procedure the query takes 3 minutes. When
executed in query analyzer it takes 7 seconds. Why would the execution plan
s
be so different?
The reason the stored procedure plan takes so long is it does an index scan
and then a bookmark lookup of more than 6 million rows. The ultimate result
set is only a few rows so this is definately not needed. When run in query
analyzer only the rows needed are retrieved.
Thanks,
DerrickParameter sniffing... your stored procedure is likely cached with a bad plan
due to atypical or non-optimal parameter values.
http://tinyurl.com/rkgbm
"Derrick Pope" <DerrickPope@.discussions.microsoft.com> wrote in message
news:8692616D-CF3E-411E-B17C-C95732A59BEE@.microsoft.com...
>I have a stored procedure that is taking too long to execute. In trying to
> diagnose the problem and come up with a solution, I executed the query the
> stored procedure runs in query analyzer and it ran very fast. I compared
> the
> two execution plans and they are vastly different.
> to clarify I took the text of the stored procedure and copied it into
> query
> analyzer. The stored procedure takes 2 paramaters. I declared the 2
> parameters as variables and set them to the values that were being passed
> into the stored procedure.
> When executed in the stored procedure the query takes 3 minutes. When
> executed in query analyzer it takes 7 seconds. Why would the execution
> plans
> be so different?
> The reason the stored procedure plan takes so long is it does an index
> scan
> and then a bookmark lookup of more than 6 million rows. The ultimate
> result
> set is only a few rows so this is definately not needed. When run in
> query
> analyzer only the rows needed are retrieved.
> --
> Thanks,
> Derrick