Linked Server Performance and options

Question

At work we have two servers, one is running an application a lot of people use which has an SQL Server 2000 back end. I have been free to query this for a long time but can't add anything to it such as stored procedures or extra tables.

This has lead to us having a second SQL Server linked to the first one and me building up a library of stored procedures that query data from both sides using linked server. Some of these queries are taking longer than what I would like.

Can someone point me to some good articles about using linked servers? I am particularly interested in finding out what data is being transferred between the two as usually the majority of the sql statement could be performed remotely but I have the feeling it may be transferring the full tables, it is usually just a join to a small final table locally.

Also what do the linked server options do I currently have:

• Collation Compatible True
• Data Access True
• Rpc True
• Rpc Out True
• Use Remote Collation False
• Collation Name (Blank)
• Connection Timeout 0
• Query Timeout 0

EDIT:

Just thought I would update this post I used openqueries with dynamic parameters for a while to boost performance, thanks for the tip. However doing this can make queries more messy as you end up dealing with strings. finally this summer we upgraded SQL Server to 2008 and implemented live data mirroring. To be honest the open queries were approaching the speed of local queries for my tasks but the mirroring has certainly made the sql easier to deal with.

Answer 1

Avoid joins to linked server tables.

Using a four part naming for your join can be used but is more expensive. Your join could contain criteria that can be used to limit the data set from the linked server and use the indexed columns.

Example:

SELECT loc.field1, lnk.field1
FROM MyTable loc
INNER JOIN RemoteServer.Database.Schema.SomeTable lnk
  ON loc.id = lnk.id
  AND lnk.RecordDate = GETDATE()
WHERE loc.SalesDate = GETDATE()

This query is also applying a criteria in the join that can be used by the linked server before the join is calculated.

The recommended method is the use of OPENQUERY.

By avoiding the join with the use of OPENQUERY the local server only sends the query to be executed remotely instead sending a set of IDs for the join.

Use the link to retrieve a set of data and perform the calculations locally. Either use a temporary table (for ad hoc queries) or insert the row in a permanent table in a nightly job.

Begining transactions may fail depending if the remote transaction coordinator is set in the liked server. Using it will consume more resources.

Also consider that you are hitting a production server running an application, while you do not specify it, I think is safe to assume that is using heavy transactions and doing inserts and updates. You are taking away resources away from the application.

Your purpose appears to be the use of the data for reporting purposes. Your server can be set to have a simple log instead of full making it more efficient.

You will also avoid your queries to be canceled due to data movement on the linked server. Always be mindful of setting the proper isolation level for your queries and table hints like NOLOCK.

And PLEASE! Never place an OPENQUERY (or any linked server) inside a loop!