SQL Server Performance

Trouble getting results from Linked server

Discussion in 'T-SQL Performance Tuning for Developers' started by smeeks, Mar 4, 2003.

  1. smeeks New Member

    I have two linked servers with collation compatibility set. The server to which I connect, call it server01, has a small temp table which needs to be joined with a very large table on the remote server02.
    I want to send the small temp table over the server02. Have the join occur on server02, return the small result set and insert that result set into a table on server 01.

    The following technique worked perfectly when server01 was a SQL 7 server. I started having problems when we set up a new SQL2000 server as server01.

    INSERT INTO ##MyTempTable
    SELECT * FROM OPENQUERY(benrosql03,'SELECT
    a.Field1
    ,a.Field2
    FROMserver02.my02db.dbo.BigTable a
    INNER JOIN
    OPENQUERY(server01,''SELECT * FROM ##SmallTable'') b
    ON a.Field1 = b.Field1')

    Note the inner Openquery will send the small table to the BigTable server02 where it will be joined with the big table. The results of the join are sent back to server01. This worked fine until we switched server01 from SQL7 to SQL2000. Now the query goes on forever until I cancel it. Even then it takes a long time, eventually reaching a timeout.

    In an attempt to work around this problem, I tried changing strategies. Rather than nested Openqueries, I tried to replace the outer nested query with a server02.master.dbo.sp_ExecuteSQL as below.

    DECLARE @cNSQL NVARCHAR(4000)
    DECLARE @cSP_ExecuteSQL VARCHAR(255)
    SET @cSP_ExecuteSQL = 'server02.Master.dbo.sp_executeSQL'
    SET @cNSQL =
    'SELECT
    a.Field1
    ,a.Field2
    FROM
    server02.my02db.dbo.BigTable a
    INNER JOIN
    OPENQUERY(server01,''SELECT * FROM ##SmallTable'') b
    ON a.Field1 = b.Field1'

    -- INSERT INTO ##MyTempTable
    EXECUTE @cSP_ExecuteSQL @cNSQL

    If the INSERT INTO ##MyTempTable line is commented out as shown, the results return to QA almost immediately. If I uncomment that line, the query never finishes and when I finally cancel it, it takes a long time before it will stop and then gives the follwing messages.

    Query cancelled by User
    [Microsoft][ODBC SQL Server Driver]Operation canceled
    [Microsoft][ODBC SQL Server Driver]Timeout expired
    ODBC: Msg 0, Level 16, State 1
    Communication link failure

    Connection Broken

    So far I have not found any way to perform the join on server02 and get the results into a table on server01. As long as I am content to look at the results in QA, its fine. If I try to insert the result set into a table, my query never finishes until I cancel it. This was working beautifully until we change from SQL7 to 2000 on server01. Server02 has always been on SQL2000. We have check collate compatibility settings.

    Any ideas on how to get this working again. Thanks.
  2. Argyle New Member

    What was the problem or error message with the first query?

    /Argyle
  3. smeeks New Member

    quote:Originally posted by Argyle

    What was the problem or error message with the first query?

    /Argyle

    It just never finished. Even after hours. The results were previously returned very quickly.
  4. satya Moderator

    First of all check whether any network problems between 2 servers.

    Make sure that your largest column is the last column retuned. Make sure that you do not have any columns returned after a text or an image column.

    Satya SKJ
  5. Argyle New Member

    While the query is running do a profiler trace (include errors) on both servers to see if you see something suspicious like many slow sp_cursoropen statments.

    /Argyle
  6. smeeks New Member

    The trace indicates nothing is happening. There are no events coming thru. Its just hung.
  7. satya Moderator

    Does any of the columns has TEXT datatypes?

    Satya SKJ
  8. smeeks New Member

    quote:Originally posted by satya

    Does any of the columns has TEXT datatypes?

    Satya SKJ


    No. Theres nothing but simple data types. Thanks.
  9. smeeks New Member

    Got Microsoft working with my DBA on it. So far, the official line is 'loop backup queries are no longer supported in 2000.' However, so far their workarounds are not working either.
  10. smeeks New Member

    Here's the answer folks. Our 2000 servers were created by imaging an original build. Consequently, they all had the same identifying ID. The Distributed transaction manager couldn't tell the servers apart. Thanks for your inputs.

Share This Page