Matan Yungman is a Technical Evangelist and senior consultant at Madeira SQL Server Services. His job is to get the word out about SQL Server. He speaks, lectures, writes, teaches and consults about SQL Server, focusing mainly on performance tuning, database development, high availability and database design. He’s passionate about SQL Server, technology and the SQL Server community.
When working with linked servers, the most expensive factor to consider is the amount of data that will have to travel over the network.
It’s important to write the right code on the right server, since each mistake can be very expensive in this area.
Below are a few common mistakes when working with linked servers:
1. Using push rather than pull
* Linchi uses openquery to illustrate the difference, but this also happens when using four part name.
2. Using Join
In order to perform a join between two servers, SQL Server has to transfer the data from one server to another. When working with very large tables, this can be very painful. By dafault, the data will be transferred from the remote server to the local server.
In order to prevent this, limit the amount of data that will be transferred between the servers. You can do this by filtering the data in the where clause, having a remote stored procedure that will bring only the relevant data, or, in case you have an inner join where the local table is much smaller than the remote one, by using the REMOTE join hint
, that will transfer data from the local server to the remote server in order to perform the join.
3. Using Union
As with joins, uniting two sets between servers will of course lead to transferring the data from the remote one.
But even if you run a remote query that units two sets from the same remote server, the sets will first be transferred to the local server, and only then be united.
Prevent this by having a remote procedure, function or view that will do the union.
4.Writing too complex queries
The optimizer cannot always know what you mean, especially when working with linked server queries.
For example, I encountered a query that looked like this one and ran for 10 minutes:
WHERE SomeColumn <
WHERE SomeColumn > 100)
I changed the query to look like this:
DECLARE @Count INT
SELECT @Count = COUNT(*)
WHERE SomeColumn > 100
WHERE SomeColumn < @Count
After the rewrite, the query ran for 1 only second..
Keep it simple!
5. Working with linked server when the databases are on the same instance.
The performance penalty here is not as dramatic as in the other bullets, but working this way is slower than using three part name inside the instance. If you identify that kind of situation, test and compare performance between the two methods on your test system. Then decide if the performance improvement is worth changing the code in production. In some situations it will.