Execution Plan - Step Cost - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Execution Plan - Step Cost

Author

Topic

saglamtimur
Yak Posting Veteran

91 Posts

Posted - 2004-03-27 : 23:49:05

When I execute below statement on Northwind, I saw some steps have cost>100% (eg:Customers.PK_Customers = 200%)

SELECT ProductName, Total=SUM(Quantity)
FROM Products P, [Order Details] OD, Orders O, Customers C
WHERE C.CustomerID = 'ALFKI'
AND C.CustomerID = O.CustomerID AND O.OrderID = OD.OrderID AND OD.ProductID = P.ProductID
GROUP BY ProductName

What I need to know is, how does it calculate this cost? I mean, for example, Customers.PK_Customers 200% of what?

Thanks in advance.

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2004-03-28 : 01:17:14

That's odd. The Clustered Index Seek on PK_Customers shows a cost of 16% on my pc, and the individuals steps do add up to 100%. What version are you running?

OS

saglamtimur
Yak Posting Veteran

91 Posts

Posted - 2004-03-28 : 13:46:02

SQL 2000 sp3 on Windows 2003 standart server

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-03-28 : 13:53:22

I just ran it on one of my servers at home. I came up with 16% on PK_Customers with the steps adding up to 100% also. I'm running SQL 2000 sp3a on Windows 2003 standard server also.

Are you still getting the same results? Try running sp_updatestats and DBCC FREEPROCCACHE.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

saglamtimur
Yak Posting Veteran

91 Posts

Posted - 2004-03-28 : 16:29:55

quote:
Originally posted by derrickleggett

Are you still getting the same results? Try running sp_updatestats and DBCC FREEPROCCACHE.

Same results

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-03-28 : 21:06:27

Is this in a stored procedure, or are you just running the statement in Query Analyzer?

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2004-03-29 : 00:03:05

This is might be the problem:
http://support.microsoft.com/default.aspx?scid=kb;en-us;274643
But I don't see why it should affect one machine and not the others. Do you have multiple processors? Perhaps parellelism is screwing up the execution plans...

OS

saglamtimur
Yak Posting Veteran

91 Posts

Posted - 2004-03-29 : 04:11:03

quote:
Is this in a stored procedure, or are you just running the statement in Query Analyzer?

First I ran it in a stored proc, then ran the statement in QA. But same results.

http://support.microsoft.com/default.aspx?scid=kb;en-us;274643

seems to be the problem, but it says "When that SELECT statement is run outside of the stored procedure, the estimated execution plan looks normal.", no it doesnt.

quote:
Do you have multiple processors

No single processor.

Northwind db is the original, I mean I did nothing on it. Just running it as it is installed. So I installed another instance of sql server, restart pc, tried both sp and single statement in QA on new instance but all same. I think its a issue like MS mentioned above KB, but has different behaviour.

Anyway thanks all for your help, I just want to know if cost > 100% is normal, ok I learned, it doesnt.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources