Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
Log in  ::  Register  ::  Not logged in

Matan Yungman

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.

5 Performance Killers When Working With Linked Servers

Linked ComputersWhen 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

Surprisingly, pushing data using linked server is much slower than pulling data. Linchi Shea wrote a good post about it.
* 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:
FROM LocalTable
WHERE SomeColumn <
 FROM RemoteServer.SomeDB.dbo.SomeTable
 WHERE SomeColumn > 100)

I changed the query to look like this:

SELECT @Count = COUNT(*)
FROM RemoteServer.SomeDB.dbo.SomeTable
WHERE SomeColumn > 100

FROM LocalTable
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.


Leave a comment on the original post [, opens in a new window]

Loading comments...