Query Plans differs

Have you tryed updating the statistic ?
Try doing an
exec sp_createstats
exec sp_updatestats
Peter
"Real knowledge is to know the extent of one's ignorance."
Confucius

>--Original Message--
>Why shoudl the query plan derived by an SP and one on
Query Analyser would
>differ widely?
>I have a system that has been running stably for a while
now when one of the
>queries started behaving badly. The SP version of the
query comes out with
>an estimated cost of 0.311 whereas if I take the same
TSQL and paste it into
>Query Analyser it estimates as 0.153.
>Unfortuately, the query plan cost isn't the whole story
as when estimated
>the SP performs about 5m page reads whereas the explicit
code only takes 800
>page reads - this makes a big difference in execution
time :-)
>Any ideas how to solve this - I've tried WITH RECOMPILE
on the SP, but it
>doesn't effect the QP choosen.
>Regards
>Paul
>
>.
>Yes, the statistics are updated before the run - thanks for the suggestion
though
Paul
"Peter The Spate" <anonymous@.discussions.microsoft.com> wrote in message
news:277501c51ffa$c4d314e0$a501280a@.phx.gbl...
> Have you tryed updating the statistic ?
> Try doing an
> exec sp_createstats
> exec sp_updatestats
> Peter
> "Real knowledge is to know the extent of one's ignorance."
> Confucius
>
> Query Analyser would
> now when one of the
> query comes out with
> TSQL and paste it into
> as when estimated
> code only takes 800
> time :-)
> on the SP, but it|||The only other thing I can think of is to delete your sp
then immediatly re-create it, it does sound like your sp
has gone a bit odd.
BTW my other sugestion about updating the stats was
garbage (when I had a chance to think about it)
Peter
"A man is never more truthful than when he acknowledges
himself a liar."
Mark Twain

>--Original Message--
>Yes, the statistics are updated before the run - thanks
for the suggestion
>though
>Paul
>"Peter The Spate" <anonymous@.discussions.microsoft.com>
wrote in message
>news:277501c51ffa$c4d314e0$a501280a@.phx.gbl...
ignorance."
while
explicit
>
>.
>|||I've tried re-creating the SP a couple of times with no effect - as I said
it's really odd as this procedure has been in production since November and
it started behaving oddly a couple of days ago, with no real change in the
underlying data volume or distribution :-(
Paul
"Peter The Spate" <anonymous@.discussions.microsoft.com> wrote in message
news:3c9a01c5200f$5676c630$a601280a@.phx.gbl...
> The only other thing I can think of is to delete your sp
> then immediatly re-create it, it does sound like your sp
> has gone a bit odd.
> BTW my other sugestion about updating the stats was
> garbage (when I had a chance to think about it)
> Peter
> "A man is never more truthful than when he acknowledges
> himself a liar."
> Mark Twain
>
>
> for the suggestion
> wrote in message
> ignorance."
> while
> explicit