Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2009

    Unanswered: link server query issue

    Hi All,

    we have an issue with SQL Link server query in our migration Project.
    There is a link Server connecting Server A with Server B (both SQL Server DB's).

    As part of a migration Project, we have cloned application DBs to a new env .
    Correspondingly we have set up Server A1 (in place of Server A) and Server B1 (in place of Server B).
    Similar Link Server (as in the old env) is set up on Server A1 (Connecting to Server B1).


    Try the same Query on both env

    Select username,userid,address from B.UserDB.dbo.User where userID = @userID


    Result comes up immediately on old env , while same takes more than 20 sec on new env.
    While comparing query plan on both env, we could see that remote link server query on new env pulls out the entire data without applying filter and later on applies where clause locally on the result.

    This is resulting in pulling lot of rows across the LInk Server resulting in query performance.

    Query plan on old env is different form this .
    On old env, Remote link server query pulls out filtered data from the target server.
    hence we have lesser data pull across servers resulting in faster query.

    Note that data and code is the same on both the env.

    Any clue why there is a difference in behaviour ..?

    NB: i replaced parameter with hardcode value and query came up immediately as in old env.
    i could also notice a change in query plan in this case (now filter was applied remotely)

  2. #2
    Join Date
    Jun 2004
    Long Island
    update statistics, or if it is 2005 may be parameter sniffing issue.

  3. #3
    Join Date
    Feb 2009
    All servers run SQL 2000.
    Actually Query is against a view called User .
    Should i update statistics on all underlying tables in the view ..?

    Select username,userid,address from B.UserDB.dbo.User where userID = @userID

  4. #4
    Join Date
    Feb 2009
    We also tested this issue by creating a new link server to SErver B1 (our source) from another SQL Server C1.Here everything works fine (unlike on Server A1).

    This makes it clear that there is no issue with the Source B1 .

    I have another doubt now : Can query plan be influenced by network traffic ..?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts