wrong execution plan on a remote query in SQL server 2005

Question

wrong execution plan on a remote query in SQL server 2005

Strider-839853

Grasshopper

Points: 13
More actions
August 5, 2008 at 6:53 am

#125489

Hi
I have 3 servers: ServerA, ServerB and ServerC
ServerA and ServerB have both SQL 2000 ServerC is SQL 2005.
I have a very simple query:
select top 1 column1 from
ServerA.Database1.dbo.Table1
order by Column1 desc
(Where serverA is defined as a linked server)
When I run this query from ServerB I get the result in few milliseconds.
When I run this query from ServerC (SQL 2005) I get the result in 30 seconds!!!
When examining the query plan in both cases its clear that in the case of serverB the query is executed on serverA and then only one row is returned. When run from ServerC then 10 million rows are returned then sorting is performed locally.
Can anyone please explain to my why this big difference in execution plan. How can I get the query to run on the remote server and return just one row when executed from ServerC.
I have all the newest service packs, I even tried installing Cumulative upgrade 8 on the sql 2005 server. The linked servers are defined the same and the servers are on the same network.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

Linked servers are quite tricky to work with...

If you want the best performance, you have to use OPENQUERY and move everything you can in the remote query, in order to retrieve the smallest amount of rows possibile. When you work with four-part-names, what happens very often is that SQLServer copies the whole table from the remote server to the tempdb of the local server and then performs the operation you requested (joins, order by etc).

With OPENQUERY you can narrow the number of rows returned by the remote server or get them in the correct order using the appropriate indexes remotely, which is not guaranteed locally in tempdb.

So your query could look something like:

SELECT *

FROM OPENQUERY(ServerA, '

SELECT *

FROM Database1.dbo.Table1

ORDER BY Column1 DESC

')

Hope this helps!

Gianluca

-- Gianluca Sartori

Pall A. Gudmundsson SSC Enthusiast Points: 107 More actions · Answer 2

Thanks for the reply Gianluca. It works really smoothly this way!

But this means that all the code has to be rewritten when transferred to SQL server 2005!

Any idea why sql 2005 behaves so differently? It seems like a huge step back.

spaghettidba SSC Guru Points: 105732 More actions · Answer 3

Try working with the linked server options, maybe you can work around your problem. This looks interesting:

(From BOL)

Collation Compatible
Affects Distributed Query execution against linked servers. If this option is set to true, Microsoft SQL Server assumes that all characters in the linked server are compatible with the local server, with regard to character set and collation sequence (or sort order). This enables SQL Server to send comparisons on character columns to the provider. If this option is not set, SQL Server always evaluates comparisons on character columns locally.
This option should be set only if it is certain that the data source corresponding to the linked server has the same character set and sort order as the local server.

-- Gianluca Sartori