SQL Server linked server performance

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.

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.

You're going to take a bit of a hit to transfer the results across the wire twice (linked server to SQL Server to your machine). Secondly, it's got to resolve the name and log in, which isn't much of a hit, but it's a hit nonetheless.

Anyway, I've found the only major bottleneck is jumping servers, since it has to transmit the information twice.