UPDATE not working properly with linked server

Question

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.

Answer 1

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.

• w0051977: 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.
• RLF: Edited to add additional information on possible issues you are facing.
• w0051977: Thanks. I will speak to my dba colleague. I assume that distribution statistics are only accessed when running a remote query?
• RLF: The remote distribution statistics are only accessed when running a remote query.
• w0051977: Thanks. Do you believe they may be causing the problem?