"Show Execution Plan" and misleading query costs...

Will Atkinson

Hi All,

I'm a relative newbie to SQL Server, so please forgive me if this is a
daft question...

When I set "Show Execution Plan" on in Query Analyzer, and execute a
(fairly complex) sproc, I note that a particular query is reported as
having a query cost of "71% relative to the batch" - however, this is
nowhere near the slowest executing query in the batch - other queries
which take over twice as long are reported as having costs in the
order of a few percent each.

Am I misreading the execution plan? Note that I'm looking at the
graphical plan, and am not reading the 'estimated' plan - I'm using
the one generated from executing the sproc. My expectation was that
this would be based on the execution times of the queries within the
sproc, however, this does not appear to be the case. (Note - I
determined execution times from PRINT statements, using GETDATE() to
determine the current time, down to milliseconds).

Any feedback would be of great assistance... I may well have to
change the way I approach optimizing queries based on these findings.

Thanks,

LemonSmasher.

Jul 20 '05 #1

Vaxman

This is just me, others may differ but I never use the graphical outupt.
I run set statistics profile on and set statistics io on and examine
that output for high physical then logical IO's. Examine the actual
query plan for row operations, executions, and eliminate table scans
where they contribute to high row operations and reduce io with
appropriate indexes, subqueries or alternate joins.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Jul 20 '05 #2

Erland Sommarskog

[posted and mailed, please reply in news]

Will Atkinson (Le**********@hotmail.com) writes:

When I set "Show Execution Plan" on in Query Analyzer, and execute a
(fairly complex) sproc, I note that a particular query is reported as
having a query cost of "71% relative to the batch" - however, this is
nowhere near the slowest executing query in the batch - other queries
which take over twice as long are reported as having costs in the
order of a few percent each.

While you are looking at the actual plan, all numbers you see are
estimates from the optimizer. To present the graphical plan, QA sends
the command SET STATISTICS PROFILE ON and this output does include any
statistics about actual execution time.

If you want to see execution times per statement, you can use
SET STATISTICS TIME ON, or run a Profiler trace and include the
events SP:StmtCompleted and SQL:StmtCompleted and the Duration
column.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Jul 20 '05 #3

Leythos

In article <Xn**********************@127.0.0.1>, es****@sommarskog.se
says...

[posted and mailed, please reply in news]

Will Atkinson (Le**********@hotmail.com) writes:

When I set "Show Execution Plan" on in Query Analyzer, and execute a
(fairly complex) sproc, I note that a particular query is reported as
having a query cost of "71% relative to the batch" - however, this is
nowhere near the slowest executing query in the batch - other queries
which take over twice as long are reported as having costs in the
order of a few percent each.

While you are looking at the actual plan, all numbers you see are
estimates from the optimizer. To present the graphical plan, QA sends
the command SET STATISTICS PROFILE ON and this output does include any
statistics about actual execution time.

If you want to see execution times per statement, you can use
SET STATISTICS TIME ON, or run a Profiler trace and include the
events SP:StmtCompleted and SQL:StmtCompleted and the Duration
column.

You also need to clear the cache if you want a true test. When you run a
query and then again run it, you may not see any real benefits if the
result/plan is cached from the previous run.

--
--
sp*********@rrohio.com
(Remove 999 to reply to me)

Jul 20 '05 #4