November 21, 2002 at 1:54 pm
I've noticed sometimes the "Query Cost (relative to batch)" is very high/incorrect in the execution plan, when executing remote queries against a linked server. Does anyone know what causes the inacurracy of the percentage, and how to work around it?
-Dan
-Dan
November 21, 2002 at 9:56 pm
I use quite a lot of linked servers, and examine execution plans almost daily and all of the execution plans show higher values against linked servers than they do when running that piece locally to the linked server. Is this what your asking about? If so, its the overhead of the communications and synchronization of data between the two servers. DTC, rowset transfers, etc... Also, if your not using open recordset statements, your executing locally requests for data remotely as opposed to executing remotely and retrieving a rowset. I've never known it to be incorrect though, it mearly reports counter differences from begin to end of transaction.
November 22, 2002 at 4:04 am
It is even worse if you use OPENROWSET. Any rowset function returns the same cost irrespective of what is being returned. This is very dangerous when you join to such a table.
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
November 22, 2002 at 8:01 am
I believe the value to be totally incorrect, here's why:
My stored proc is a procedure used to search a large database, but it also does a few remote queries in the beginning to get some values. These remote queries are always the same. If I run a search on a very unique word, the main query returns quickly (1 sec or less), yet the execution plan shows that 85% of the query was spent doing the simple remote query. This may be correct, but when a run a complex search that takes about 30-40 seconds, it still reports that the remote query took about 85% of the time. The remote query is the same for both of the queries. An ideas?
-Dan
-Dan
November 22, 2002 at 8:50 am
How are you doing the remote query?
linkedserver.dbname.dbo.spName
or
openquery(linkedServer,'dbname.dbo.spName')
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
November 22, 2002 at 11:36 am
Currently I'm using OPENQUERY, like this (ugly, I know):
(SELECT * FROM OPENQUERY(SERVER1, 'SELECT Client_ID, Ult_Client_ID=db1.dbo.fn_Get_Ultimate_Parent_ID(Client_ID,default) FROM db1.dbo.Client_Master'))
-Dan
-Dan
November 22, 2002 at 4:36 pm
As expected look at the query plan, you will see a remote rowset element that costs 3.36 (or something like that)
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply