Post reply

Question about How Sql Server runs queries through a linked server

  • February 28, 2014 at 11:03 am

    #288840

    We use linked servers constantly and I am seeing something interesting I never noticed before. I have a stored procedure that connects to a table on another server. What I noticed is that I see a query on the linked server that is grabbing all the data from the table without the filter that is being used in the stored procedure. It seems really odd to me. It is grabbing all records instead of just the one it needs. I hate using nolocks but it was causing blocking because it was grabbing all the data(100,000 records at least) Can anyone explain to me why it works that way and if there is anyway to fix it? Let me know if this doesn't make sense.

    This is the query I see on the linked server:

    SELECT "Tbl1003"."UserID" "Col1020",

    "Tbl1003"."RecordTimeStamp" "Col1021",

    "Tbl1003"."EmailAddress" "Col1022"

    FROM "Shared"."dbo"."USERS" "Tbl1003"

    WITH

    (

    NOLOCK

    )

    This is the query from the source server:

    SELECT PR.PracticeID

    FROM SharedServer.shared.dbo.Users U WITH (nolock)

    INNER JOIN dbo.UserPractices UP ON UP.UserID = U.UserID

    INNER JOIN dbo.Practice PR ON PR.PracticeID = UP.PracticeID

    WHERE U.EmailAddress = @userName

    AND PR.Active = 1

  • February 28, 2014 at 12:11 pm

    #1693227

    There is only limited ability of distributed queries to get and use statistics, so, when it can't get them, it just moves the entire data set over. You can read more about it in this great blog post by Benjamin Nevarez[/url]. He links to the MSDN article that might be able to help you some. In general, I try really hard to stay away from queries to linked servers, and if I have to, I'll usually use OPENQUERY to offload the processing to the other machine as much as possible.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • February 28, 2014 at 12:28 pm

    #1693237

    Thanks Grant for the information. I'll have to look into see if openquery will work for us.

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

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