SQL Server Performance Forum – Threads Archive

Execution plan question

Hi all, When I display the (estimated) execution plan in Query Analyzer on a computer connecting to our production server it displays 0% cost for *all* operators. When I execute it locally on the server the cost are displayed and all add up to 100% I didn’t find any info (only 1 newsgroup post) on this topic. Am I doing something wrong?? Or is this by design. Thanks,
Buckley

Remote clients should still be able to generate an estimated execution plan including costs, so there is something causing this problem. This refers to sql 2000, but applies to earlier sql versions as far as I can remember. If you display an *actual* execution plan, are the costs visible ?
Do you have autocreate statistics and autoupdate stats enabled for that database ? If not you ought to regenerate your statistics and try again.

Thanks Chappy 1) autocreate statistics and autoupdate stats are enabled for the database
I also tested the scenario on the nortwind database which i haven’t touch after installing sql server.
Same problem here. So i don’t think its db related 2) The *actual* execution plan has the same problem :
– remote all 0% cost
– local correct cost I use sql server 2000 (Build 2195: Service Pack 2) Do you have heard of this problem in the past, is it a bug? ( The only related problem I found was that some version of QA showed cost > 100% ) I took a screenshot of the problem : http://www-dev.themeparkvision.net/plan.gif

No, I’ve not heard of this being a bug, nor have I ever encountered it. The only thing I can think of remotely related, is that in profiler, some events arent available if you use tcpip instead of named pipes. What client network lib are you using on the remote ? Id be surprised if this is the cause though. Ive used both in the past, just worth checking perhaps. Also, if you explicitly request the execution plan in textual form, what happenes here? SET SHOWPLAN_ALL ON
GO
<your query>
GO
SET SHOWPLAN_ALL OFF
GO

My point about profiler point might be inaccurate. I do remember some problem with these two client libs and profiler, but now I can’t find a reference to it. Still worth checking though, id say <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />

Thanks Chappy, I use tcp/ip i guess because i connect over the internet using a domain name. How do i check what network lib the server is using? I didn’t use the showplan_all method so i suppose that the column "TotalSubtreeCost" is of impartance : stmt text : set CONCAT_NULL_YIELDS_NULL OFF ; SELECT * FROM Lid LEFT JOIN (SELECT MediaID,ID FROM Media where t |–Sort(ORDER BY<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Lid].[ViewFiche] DESC, [Lid].[Login] ASC)) |–Hash Match(Right Outer Join, HASH<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Media].[ID])=([Lid]. |–Clustered Index Scan(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[PimTech1].[dbo].[Medi |–Compute Scalar(DEFINE<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[Lid].[Media_PersonNr]=[dbo] |–Table Scan(OBJECT<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />[TPVdata].[dbo].[Lid]), WHE "TotalSubtreeCost : NULL 0.27926603 0.27926603 0.22985135 6.6253066E-2 0.12679207 0.12679207 So if we do it this way it seems to work.

Can someone try to execute the following query against northwind select * from dbo.Invoices I get a 0% cost for all the operators :
http://www-dev.themeparkvision.net/north.gif And It makes no difference if i execute it localy or remote. So my previous remark is not valid for the query "select * from dbo.[Order Subtotals]" i get correct results.
This is realy puzzling .. help pls

For the invoices query (running locally on my home pc), select and compute scalar are 0%, but then further child operations tend to have costs associated with them (eg. 7% for the inner join).
For the second query I get 18% and 82% similar to you. In hindsight I think EM uses SET STATISTICS PROFILE ON, instead of SHOWPLAN_ALL. You can try this to see if its the data being returned thats at fault, or Enterprise Managers ability to parse it correctly. I dont really know how either scenario would help you though! Some operations with insignificant cost *will* appear as 0%, but when the mouse is hovered over them, will display a small non zero cost. The fact your total batch cost is displaying 0% is very odd. I dont know what else to suggest, sorry, especially when the problem occurs locally also. Maybe some more experienced guys have some ideas?

I am going to post in on the microsoft newsgroups. I will let post a solution here if i get one Thanks for you help Chappy!

Hi all,
I finaly solved the problem through expert-exchange.com with the help of ispaleny (poinst coming you way. Thanks a lot!)
It has to do with the reginal settings! Didn’t come up with that myself.
My remote server had English(US) and the local was Belgium. After I changed the local system to English(US) the prob was gone.
So it is a bug that probably had to do with punctuatuin. Cheers

Very odd, Id never have guessed that.
Glad you fixed it!

]]>