Query Plans differs

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
PaulPaul,
The two pieces of code, TSQL script and stored procedure, are not optimized
the same because of the knowledge available at the time of optimization.
For example, the TSQL script has the variables set with the values that you
want to run.
Here is one thing that causes stored procedures to be less than optimal:
If internally to a stored proc you set a variable, the optimizer (which has
no value for the variable when it optimizes) may have selected a suboptimal
plan. So, if this is your case, rather than:
DECLARE @.MyVal INT
SET @.MyVal = 123
SELECT ... WHERE MyCol = @.MyVal
You could do:
DECLARE @.MyVal INT
SET @.MyVal = 123
EXEC AnotherStoredProcedure @.MyVal
The called stored procedure will optimize with the value supplied in @.MyVal.
Remember, of course, that recompiles of stored procedures are common, so
this is not as simple as black and white.
And, of course, there are other factors that could mislead the optimizer.
RLF
"Paul Hatcher" <phatcher@.spamless.cix.co.uk> wrote in message
news:uU1mWm$HFHA.1580@.TK2MSFTNGP10.phx.gbl...
> 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
>|||Thanks, there was one such variable, so I changed the code as per your
suggestion - reduced it from 5m reads to around 4.5m :-)
The optimiser normally does such a good job that it's just a pain when it
gets it wrong. Given the nature of this query, it's almost impossible to put
any useful hints that wouldn't cause much more pain elsewhere in the system.
I'll have to see if I can program around it with a different query just to
get it back running - previously it took around 100ms now it takes 10s+
Paul
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:#u#Rlw$HFHA.2924@.TK2MSFTNGP15.phx.gbl...
> Paul,
> The two pieces of code, TSQL script and stored procedure, are not
optimized
> the same because of the knowledge available at the time of optimization.
> For example, the TSQL script has the variables set with the values that
you
> want to run.
> Here is one thing that causes stored procedures to be less than optimal:
> If internally to a stored proc you set a variable, the optimizer (which
has
> no value for the variable when it optimizes) may have selected a
suboptimal
> plan. So, if this is your case, rather than:
> DECLARE @.MyVal INT
> SET @.MyVal = 123
> SELECT ... WHERE MyCol = @.MyVal
> You could do:
> DECLARE @.MyVal INT
> SET @.MyVal = 123
> EXEC AnotherStoredProcedure @.MyVal
> The called stored procedure will optimize with the value supplied in
@.MyVal.
> Remember, of course, that recompiles of stored procedures are common, so
> this is not as simple as black and white.
> And, of course, there are other factors that could mislead the optimizer.
> RLF
> "Paul Hatcher" <phatcher@.spamless.cix.co.uk> wrote in message
> news:uU1mWm$HFHA.1580@.TK2MSFTNGP10.phx.gbl...
would
with
estimated
it
>|||Paul,
Maybe if you post the query in question (accompanied with the relevant
DDL) someone might be able to give a more specific advice...
Gert-Jan
Paul Hatcher wrote:
> Thanks, there was one such variable, so I changed the code as per your
> suggestion - reduced it from 5m reads to around 4.5m :-)
> The optimiser normally does such a good job that it's just a pain when it
> gets it wrong. Given the nature of this query, it's almost impossible to p
ut
> any useful hints that wouldn't cause much more pain elsewhere in the syste
m.
> I'll have to see if I can program around it with a different query just to
> get it back running - previously it took around 100ms now it takes 10s+
> Paul
> "Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
> news:#u#Rlw$HFHA.2924@.TK2MSFTNGP15.phx.gbl...
> optimized
> you
> has
> suboptimal
> @.MyVal.
> would
> with
> estimated
> it|||Gert-Jan
Thanks for the offer, but it's too complicated - as an example I would guess
that the query pans out to about a 30 table join, mostly self-joins as it's
a triple-based schema, but still fairly hairy.
I've now managed to solve it by a different structured query.
Paul
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:422766C1.A0FE26A1@.toomuchspamalready.nl...
> Paul,
> Maybe if you post the query in question (accompanied with the relevant
> DDL) someone might be able to give a more specific advice...
> Gert-Jan
>
> Paul Hatcher wrote:
it
put
system.
to
optimization.
that
optimal:
(which
so
optimizer.
one of
out
it
takes
butsql

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