new eyes

Distributed transactions, over linked servers, incur more overhead than transactions occurring on the same server. This is due, in part, to the fact that more than one server is involved in the transaction and more network traffic is generated. Because of the overhead involved in distributed transactions, they should be avoided when they can be avoided. In other words, only use distributed transactions when there are no alternatives available to accomplish your goal. [6.5, 7.0, 2000, 2005] Updated 8-7-2006

*****

If you need to access data from a remote server from within a query, it is more efficient to perform a linked server query (after having created a linked server) rather than using an ad hoc query that uses the OPENROWSET or the OPENDATASOURCE functions. [7.0, 2000, 2005] Updated 8-7-2006

*****

By default, when you run a distributed query using a linked server, the query is processed locally. This may or may not be efficient, depending on how much data must be sent from the remote server to the local server for processing. Sometimes it is more efficient to pass through the query so that it is run on the remote server. This way, if the query must process many rows, it can process them on the remote server, and only return to the local server the results of the query. The OPENQUERY function is used to specify that a distributed query be processed on the remote server instead of the local server. [7.0, 2000, 2005] Updated 8-7-2006

*****

When running distributed queries on a linked server, if the linked server has the same character set and sort order (collation) as the local SQL Server, then you can reduce overhead and boost performance if you set the SP_SERVEROPTION “collation compatible” option to true. What this setting does is tell SQL Server to assume that all columns and character sets on the remote server are compatible with the local server. This same option can also be turned on for a linked server using Enterprise Manager or Management Studio.

If this option is not selected, then the distributed query being executed on the remote server must return the entire table to the local server in order for the WHERE clause to be applied. As you can imagine, this could potentially return a lot of unnecessary data over the network, slowing it down.

If the option is selected, (which is always recommended if the collations are the same on both servers), then the WHERE clause is applied on the remote server. This, of course, means that much less data is transmitted over the network, often greatly speeding up the distributed query. [7.0, 2000, 2005] Updated 8-7-2006

*****

If you run many distributed queries through linked servers, you will want to ensure that the connection between the linked servers is fast. Ideally, the linked servers should be connected to the same switch, or at least be in the same subnet. [7.0, 2000, 2005] Updated 8-7-2006

*****

In SQL Server 2000 and 2005, when creating a linked server, in the Server Options tab, there is an option called Connection Timeout and Query Timeout. The default setting for both of these options is 0, which means that there is no timeout value for either of these options. This means that long running remote queries will not time out.

If you suspect that some of the remote queries that will be run may take “too long” and unnecessarily use up too many server and network resources, you can enter a time in seconds that you are willing to wait for a remote query to run, but if the query takes longer than the amount you have specified, then it will be aborted. You can use this feature to ensure that no long running queries take up more resources than they should. [2000, 2005] Updated 8-7-2006

*****

When you create a link between two SQL Servers, SQL Server does its best to perform as much work as it can on the remote server. This way, less data has to be moved from the remote server to the local server, helping to reduce overhead and boosting performance. But for some particular operations, they have to be performed on the local server, not the remote server. Some examples of locally performed operations include:

• Data conversion operations
• Queries that use the bit, timestamp, or uniqueidentifier data types
• Queries that use the TOP clause
• INSERTS, UPDATES, or DELETES

Because of this, you may want to try to avoid performing any of the above operations using a remote linked server.

If you are running a remote query against a linked server, and you want to find out which parts are performing on the remote server and which are performing on the local server, run the query from Query Analyzer or Management Studio and take a look at the query plan. It will tell you what part of your query is running where. It should be your goal to create code that runs mostly on the remote server, not the local server. [7.0, 2000, 2005] Updated 8-7-2006