Post reply

wrong execution plan on a remote query in SQL server 2005

  • 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.

  • August 5, 2008 at 8:59 am

    #853238

    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

  • August 6, 2008 at 2:51 am

    #853672

    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.

  • August 6, 2008 at 3:02 am

    #853674

    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

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

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