How to Achieve High Performance with Linked Servers
By Kalman Toth, M.Phil. Physics, M.Phil. Computing Science, MCDBA, MCITP

February 10, 2009

Linked servers may be few feet apart in the same computer room, or thousands of miles away, one in New York, the other in London. Communications with a linked server are significantly slower than communications with a database on the same server. The reason: instead of the motherboard and native Windows communications, the information exchange has to go through network boxes, security authentication and additional layers of communications software.

You can join tables in different databases on different servers: cross database, cross server query. Here is an example:

SELECT a.*,

       b.*

FROM   TableA a

       INNER JOIN DATASRV888.accounting.dbo.TableB b

         ON a.ColumnP = b.ColumnQ

For complex queries the performance may be unpredictable. That depends how the query optimizer prepares the execution plan. SQL Server may copy over the entire TableB before executing the join. If the table is small, and the communications link is fast, it is not an issue. However for large tables and/or slow links, it is a performance killer. On a typical platform, to copy 5 GB of data file from the linked server to another on the Windows level may take 10 minutes. To copy over a table 5 GB from database to database may take much longer during normal operations. Here is a simple test with small tables and linked servers on same physical server or connected with few feet of LAN cable through a network switch:

-- JOIN tables in same database

USE AdventureWorks;

DECLARE @StartTime datetime

DBCC DROPCLEANBUFFERS

SET @StartTime = getdate()

SELECT SlsOrds = count(*)

FROM Sales.SalesOrderHeader soh -- 31K

INNER JOIN Sales.SalesOrderDetail sod -- 121K

on soh.SalesOrderID = sod.SalesOrderID

SELECT DurationMsec = datediff(ms, @StartTime, getdate())

GO

-- 121313

-- 50 msec (avg)

-- JOIN tables in different databases on same server instance

DECLARE @StartTime datetime

DBCC DROPCLEANBUFFERS

SET @StartTime = getdate()

SELECT SlsOrds = count(*)

FROM Sales.SalesOrderHeader soh

INNER JOIN CopyALPHAOfAdventureWorks.Sales.SalesOrderDetail sod --121K

on soh.SalesOrderID = sod.SalesOrderID

SELECT DurationMsec = datediff(ms, @StartTime, getdate())

GO

-- 121313

-- 120 msec (avg)

Related msdn forum link:

http://social.msdn.microsoft.com/Forums/en/transactsql/thread/250dac80-f35c-46ea-9bf6-2489e055280d

-- JOIN tables in different databases on different server instances (linked server)

DECLARE @StartTime datetime

-- DBCC DROPCLEANBUFFERS -- EXECUTE it on DELLSTAR\ALPHA

DBCC DROPCLEANBUFFERS

SET @StartTime = getdate()

SELECT SlsOrds = count(*)

FROM Sales.SalesOrderHeader soh

INNER JOIN [DELLSTAR\ALPHA].AdventureWorks.Sales.SalesOrderDetail sod --121K

on soh.SalesOrderID = sod.SalesOrderID

SELECT DurationMsec = datediff(ms, @StartTime, getdate())

GO

-- 121313

-- 500 msec (avg)

-- JOIN tables in different databases on different servers (linked server)

DECLARE @StartTime datetime

-- DBCC DROPCLEANBUFFERS – EXECUTE it on GATESTAR

DBCC DROPCLEANBUFFERS

SET @StartTime = getdate()

SELECT SlsOrds = count(*)

FROM Sales.SalesOrderHeader soh

INNER JOIN [GATESTAR].AdventureWorks.Sales.SalesOrderDetail sod --121K

on soh.SalesOrderID = sod.SalesOrderID

SELECT DurationMsec = datediff(ms, @StartTime, getdate())

GO

-- 121313

-- 400 msec (avg)

-- Execute stored procedure on linked server (Remote Procedure Call)

-- Linked server must be configured for RPC

-- Use Linked Server properties to set RPC /RPC OUT true

/* Create stored procedure on linked server GATESTAR

CREATE PROCEDURE sprocSalesOrderCount

AS

SELECT SlsOrds = count(*)

FROM AdventureWorks.Sales.SalesOrderHeader soh

INNER JOIN AdventureWorks.Sales.SalesOrderDetail sod --121K

on soh.SalesOrderID = sod.SalesOrderID

GO

*/

DECLARE @StartTime datetime

-- Execute DBCC DROPCLEANBUFFERS on GATESTAR

DBCC DROPCLEANBUFFERS

SET @StartTime = getdate()

EXECUTE [GATESTAR].AdventureWorks.dbo.sprocSalesOrderCount

SELECT DurationMsec = datediff(ms, @StartTime, getdate())

GO

-- 121313

-- 38 msec (avg)

The linked server performance on the same server is the slowest probably due to disk contention. The linked server performance 8 times slower for a cross database, cross server query.

There is another aspect to linked server performance. Even if the 38 msec RPC execution or the 400 msec cross server query execution time is acceptable, due to network delay, the response occasionally can increase to 20, 000 msec, for example, in other words subject to unpredictability.

There are several ways you can take control back.

First, you can build and execute a stored proc on the linked server. That is fast. Example:

EXEC DATASRV888.accounting.dbo.WeeklyAccountingSummary

Second, you can pull the data you need from the linked server into a temporary or staging table. After that you operate with the data on same server at the usual speed. The advantage of this approach is that you can execute this step individually and get timing on the transfer. Example:

SELECT *

INTO   #TableB

FROM   DATASRV888.accounting.dbo.TableB

WHERE  ColumnR = '2010'

Third, you can "push" the data from the linked server instead of "pulling" it. The advantage of this approach is that the data partitioning takes place on the linked server efficiently, and only the needed data is moved. One way to push the data is a stored procedure on the linked server which is called from the "local" server.

The discussion above assumed high speed linking (1 GB/min) between the servers. With slow links, you have to be extremely careful. Basically, you just want to pull a single row with a remote procedure call or with OPENQUERY. If you need some significant data transfer, the best to schedule it as night process or physical transfer on CD, DVD or external disk drive.

DECLARE @StartTime datetime;

DBCC DROPCLEANBUFFERS;

-- Execute DBCC DROPCLEANBUFFERS on GATESTAR

SET @StartTime = getdate();

SELECT * FROM OPENQUERY( GATESTAR,

'SELECT SlsOrds = count(*)

FROM AdventureWorks.Sales.SalesOrderHeader soh -- 31K

INNER JOIN AdventureWorks.Sales.SalesOrderDetail sod -- 121K

on soh.SalesOrderID = sod.SalesOrderID;

')

SELECT DurationMsec = datediff(ms, @StartTime, getdate())

GO

-- 121313

-- 40 msec (avg)

The OPENQUERY above is even faster than execution on the local DELLSTAR server since the query is executed on the linked server and only the few bytes of result is transferred on the network. Nevertheless it is still subject to unpredictability due to network performance.

A WORKAROUND suggested by Paul Nielsen (http://www.sqlserverbible.com/) : don't use linked servers, instead push the task into the application layer because the application can connect to both servers at the usual (high) speed.