SQL Server linked server performance

Question

I am using SQL Server 2008 Enterprise. And I am using Linked Server technologies to link another SQL Server 2008 Enterprise instance from another server. I write TSQL to manipulate objects (e.g. tables) from both server instances.

My question is, for linked server, is there a big performance issue? If yes, what is the key performance bottleneck and best practice we should follow?

thanks in advance, George

Answer 1

It depends on what you are doing.

If you are running queries that join between tables in the two server instances, and transferring large amounts of data, then you have a bottleneck that you need to be aware of.

If the servers are on their own subnet with a 1GB link, then you should not have to worry a great deal. I would be concerned if the two servers are connected by a shared, slow link.

Answer 2

My question is, for linked server, is there a big performance issue? If yes, what is the key performance bottleneck and best practice we should follow?

Compared to what? For what queries? of course it all depends on what you are doing. For some queries the performance hit will be negligible for others massive.

There are a bunch of concerns you should keep in mind:

• If you will be joining 2 tables from DB1 to 2 tables from DB2, and the tables are big, stuff can get ugly. At the end of the day, the queries will execute somewhere. The db will have to pull all the results in to the main DB and maintain transactional integrity on the main db. This can be really expensive.
• If you start running distributed transactions stuff can get ugly, fast.
• When joining stuff across servers your indexes on the remote server can be rendered useless. The data all has to move somewhere for the joins.
• Linked server links can go down at unexpected times and lead to hard to diagnose bugs.

In the past I have found situations where it was a few orders of magnitude faster to move the remote data locally, and index it before joining into it.