SQL Server › Linked Servers

What performance issues commonly arise when querying data through a linked server, and how can you address them?

Intermediate

Answer

Queries against a linked server can perform poorly if SQL Server has to pull large amounts of data across the network before it can apply filters or joins locally, so it often helps to push as much filtering as possible to the remote query itself, use OPENQUERY to send a native query directly to the remote server, and avoid joining very large tables across the linked server connection.

SELECT * FROM OPENQUERY(RemoteServer, 'SELECT CustomerId, Name FROM Customers WHERE Region = ''West''');

Real-world example A slow report joining a large local table with a linked server table is significantly improved by rewriting the remote portion using OPENQUERY, letting the filtering happen on the remote server before the smaller result set is sent back.

Common follow-ups: What is the difference between using OPENQUERY and the standard four part naming convention?;How do you identify whether a slow query is actually caused by linked server overhead?

Query Optimization & Plans;Data Types & Schema Design