1

Please see a question I asked here earlier: https://stackoverflow.com/questions/24324823/update-not-working-properly-with-linked-server

Dommer suggested using OPENQUERY and this does appear to resolve the problem. Does anyone know why OPENQUERY resolves the problem? I have read the documentation for OPENQUERY here: http://msdn.microsoft.com/en-gb/library/ms188427.aspx, but I have not found an answer to my specific question.

Improve this question

1 Answer 1

Reset to default
2

The fundamental issue is: Which server processes the query?

When using a linked server query, the query is executed on the LOCAL server and uses the linked server to access data on the remote server. (Linked servers are subject to several limitations, some caused by running through the Distributed Transaction Coordinator (DTC).)

When using OPENQUERY, the query is sent to the REMOTE server and is executed there in order to return data to the local server. (You will notice that in OPENQUERY the query uses a three-part name, since it all runs on the remote server, rather than the four-part name used for the linked server.)

A simple exposure to the limitations can be found in this thread: http://www.sqlservercentral.com/Forums/Topic948316-338-1.aspx

It mentions network topology, data types, type of remote server, the code in the query, etc.

EDIT: See more on the Guidelines for Using Distributed Queries at: http://technet.microsoft.com/en-us/library/ms175129(v=sql.105).aspx

The subheading Other Guidelines mentions: "To create the best query plans ..., the query processor must have data distribution statistics from the linked server. ... If the linked server is an instance of SQL Server, to obtain all available statistics, the user must own the table or be a member of the sysadmin fixed server role, the db_owner fixed database role, or the db_ddladmin fixed database role on the linked server."

If your connection to the remote server does not have access to those statistics that might cause a poor plan. Ideally not an inaccurate plan, but the query processor is imperfect.

Improve this answer
6
  • Thanks, I have looked at the execution plan and the actual remote query sent to the remote server is: SELECT top 1 created from dbusns where reference='1'. This is why it is falling over. Surely it should send: select max(created) from dbusns where reference='1'. +1 for the link.
    – w0051977
    Jun 20, 2014 at 14:03
  • Edited to add additional information on possible issues you are facing.
    – RLF
    Jun 20, 2014 at 15:45
  • Thanks. I will speak to my dba colleague. I assume that distribution statistics are only accessed when running a remote query?
    – w0051977
    Jun 20, 2014 at 17:30
  • The remote distribution statistics are only accessed when running a remote query.
    – RLF
    Jun 20, 2014 at 17:35
  • Thanks. Do you believe they may be causing the problem?
    – w0051977
    Jun 20, 2014 at 17:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.