Post reply

SQL Server LInk Server Query

  • February 17, 2009 at 9:46 am

    #131073

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

    ISSUE

    Try the same Query on both env

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

    RESULT

    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)

  • March 4, 2009 at 5:36 am

    #953034

    so better u use a stored procedure in this case.

    define the stored procedure in linked server and call it ...

Viewing 2 posts - 1 through 1 (of 1 total)

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