Execution Plan in Query Analyzer

Hi
When I display Execution Plan (graphical version) in Query Analyzer
I get some steps with cost over 100% (200%, 600% etc.) or sometimes
I get costs under 100% but sum of all costs is over 100%
If I use SET SHOWPLAN_ALL for this query all look OK, and if
I display Execution Plan for this query using MS SQL Server Managment Studio
(Yukon tool)
all costs are under 100% and sum is 100%.
I have installed SP3a for SQL Server 2000 (on server and client computer).
I thing, problem is in Query Analyzer, but how correct this?
ph
Piotr,
Can you post a query against Pubs or Northwind that reproduces the problem?
"Piotr Hamrol" <piotr.hamrol@.domdata.depfa-it.pl> wrote in message
news:cghj9a$m2a$1@.helios.domdata.com...
> Hi
> When I display Execution Plan (graphical version) in Query Analyzer
> I get some steps with cost over 100% (200%, 600% etc.) or sometimes
> I get costs under 100% but sum of all costs is over 100%
> If I use SET SHOWPLAN_ALL for this query all look OK, and if
> I display Execution Plan for this query using MS SQL Server Managment
Studio
> (Yukon tool)
> all costs are under 100% and sum is 100%.
> I have installed SP3a for SQL Server 2000 (on server and client computer).
> I thing, problem is in Query Analyzer, but how correct this?
> ph
>
|||USE pubs
GO
SELECT COUNT(j.job_id)
FROM employee e INNER JOIN jobs j ON e.job_id=j.job_id
GO
GRAPHICAL EXECUTION PLAN - MS SQL Server Management Studio (Yuckon tool):
SELECT --Compute Scalar--Stream Aggregate--Nested Loops--Clustered
Index Scan
Cost: 0% Cost: 0% Cost: 0% Cost: 0%
| Cost: 79%
|
|--Clustered Index Seek
Cost: 21%
GRAPHICAL EXECUTION PLAN - Query Analyzer
SELECT --Compute Scalar--Stream Aggregate--Nested Loops--Clustered
Index Scan
Cost: 0% Cost: 0% Cost: 0% Cost: 0%
| Cost: 0%
|
|--Clustered Index Seek
Cost: 225%
Uytkownik "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> napisa w
wiadomoci news:O##wg2qiEHA.592@.TK2MSFTNGP11.phx.gbl...
> Piotr,
> Can you post a query against Pubs or Northwind that reproduces the
problem?[vbcol=seagreen]
>
> "Piotr Hamrol" <piotr.hamrol@.domdata.depfa-it.pl> wrote in message
> news:cghj9a$m2a$1@.helios.domdata.com...
> Studio
computer).
>
|||What version of SQL Server is the pubs database running under? I just
tested against SQL Server 2000 SP3 and could not reproduce your problem. If
you're using Query Analyzer against pubs running under SQL Server 2005, it's
quite possible that you'll get strange or incorrect results; representations
of query plans have changed in 2005.
"Piotr Hamrol" <piotr.hamrol@.domdata.depfa-it.pl> wrote in message
news:cgk7r4$lad$1@.helios.domdata.com...
>
> USE pubs
> GO
> SELECT COUNT(j.job_id)
> FROM employee e INNER JOIN jobs j ON e.job_id=j.job_id
> GO
> ----
--
> --
> GRAPHICAL EXECUTION PLAN - MS SQL Server Management Studio (Yuckon tool):
> SELECT --Compute Scalar--Stream Aggregate--Nested Loops--Clustered
> Index Scan
> Cost: 0% Cost: 0% Cost: 0% Cost: 0%
> | Cost: 79%
> |
> |--Clustered Index Seek
> Cost: 21%
> GRAPHICAL EXECUTION PLAN - Query Analyzer
> SELECT --Compute Scalar--Stream Aggregate--Nested Loops--Clustered
> Index Scan
> Cost: 0% Cost: 0% Cost: 0% Cost: 0%
> | Cost: 0%
> |
> |--Clustered Index Seek
> Cost: 225%
>
> Uytkownik "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> napisa
w[vbcol=seagreen]
> wiadomoci news:O##wg2qiEHA.592@.TK2MSFTNGP11.phx.gbl...
> problem?
|||I runing pubs database under SQL Server 2000 SP3,
I use only client tools from SQL Server 2005 and connect to database under
SQL Server 2000
Execution plans are identical in Query Analyzer and MS SQL Server Management
Studio 2005, but
only costs in Query Analyzer are incorrect.
I write before:
If I use SET SHOWPLAN_ALL ON option - subtreecosts are correct !!!
ph
Uytkownik "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> napisa w
wiadomoci news:u#nt5n3iEHA.2052@.TK2MSFTNGP15.phx.gbl...
> What version of SQL Server is the pubs database running under? I just
> tested against SQL Server 2000 SP3 and could not reproduce your problem.
If
> you're using Query Analyzer against pubs running under SQL Server 2005,
it's
> quite possible that you'll get strange or incorrect results;
representations
> of query plans have changed in 2005.
>
|||On Thu, 26 Aug 2004 10:35:44 -0400, Adam Machanic wrote:

>What version of SQL Server is the pubs database running under? I just
>tested against SQL Server 2000 SP3 and could not reproduce your problem. If
>you're using Query Analyzer against pubs running under SQL Server 2005, it's
>quite possible that you'll get strange or incorrect results; representations
>of query plans have changed in 2005.
Hi Adam,
I was able to reproduce this behaviour, using SQL Server 2000 SP3a. If I
execute the query posted by Piotr in Query Analyser, with the graphical
execution plan enabled (menu option "Query" / "Show Execution Plan" or
Ctrl-K), I get 5 steps listed at 0% and 1 step (the clustered index seek
on jobs.PK__jobs__...) listed at 225%. Hovering the mouse over this last
step displays "Cost: 9,000000(225%) / Subtree cost: 9,00"
SELECT @.@.VERSION
-----------
-
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Personal Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:86oti094e1uhf4aj3i69gr3uigrtfng4bj@.4ax.com...
> I was able to reproduce this behaviour, using SQL Server 2000 SP3a. If I
> execute the query posted by Piotr in Query Analyser, with the graphical
> execution plan enabled (menu option "Query" / "Show Execution Plan" or
> Ctrl-K), I get 5 steps listed at 0% and 1 step (the clustered index seek
> on jobs.PK__jobs__...) listed at 225%. Hovering the mouse over this last
> step displays "Cost: 9,000000(225%) / Subtree cost: 9,00"
Bizarre! I get the clustered index seek on jobs.PK__... as 21%, and
scan on employee.employ... as 79%, and the other three steps as 0%...
SELECT @.@.VERSION for me:
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
Only difference is you're running Personal and I'm running Standard... Could
that possibly be it?
|||Adam Machanic wrote:
> "Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
> news:86oti094e1uhf4aj3i69gr3uigrtfng4bj@.4ax.com...
> Bizarre! I get the clustered index seek on jobs.PK__... as 21%,
> and scan on employee.employ... as 79%, and the other three steps as
> 0%...
> SELECT @.@.VERSION for me:
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
> Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation
> Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
> Only difference is you're running Personal and I'm running
> Standard... Could that possibly be it?
I also get 21/79% and I'm on personal edition, but on Windows XP:
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Personal Edition on Windows NT 5.1 (Build 2600: Service Pack 1)
David G.