Speeding up Linked Servers

Linked Servers are the mechenism we use to retrieve data from remote servers. However, they can also be the cause of query performance issues. In this article, I will demonstrate how linked servers can cause performance issues in queries and how to speed up those queries by using temporary tables or moving copy of the database to a local server.

The other day a user sent me a query to optimize, because it was taking an extreemly long time to execute. Through investigation, I found that the query was run on our local production server, and joined data from two linked servers LinkedServer1 and LinkedServer2. This means, that a significant amount of data was being pulled across the network to satisfy this query.

SELECT     sc.FIRST_NAME, sc.LAST_NAME, sc.EMAIL AS sh_email, bc.EMAIL ASbem, cu.EMAIL AS cust_email,
                      sc.CONTACT_ID, od.ORDERS_ID, bc.FIRST_NAME, bc.LAST_NAME, o.SB_SEQUENTIAL_ID, o.MOZART_EXPORTED,
                      o.SB_EXPORTED, o.MOZART_SEQUENTIAL_ID, bc.CONTACT_ID AS bcid
 FROM          LinkedServer1.CustomerDB.dbo.CONTACT sc INNERJOIN
                      LinkedServer1.CustomerDB.dbo.ORDER_DETAIL od ON sc.CONTACT_ID = od.SHIP_TO_CONTACT_ID
 INNER JOIN LinkedServer1.CustomerDB.dbo.ORDERS o ON od.ORDERS_ID = o.ORDERS_ID
 INNER JOIN LinkedServer1.CustomerDB.dbo.CUSTOMER cu ONo.CUSTOMER_ID = cu.CUSTOMER_ID
 INNER JOIN LinkedServer1.CustomerDB.dbo.CONTACT bc ONcu.BILL_CONTACT_ID = bc.CONTACT_ID
                      AND     RTRIM(LTRIM(sc.EMAIL)) RTRIM(LTRIM(bc.EMAIL))
 INNER JOIN   LinkedServer2.BillingDB.dbo.tblBuyerAdditionalInfo ba ON ba.InfoValue = o.SB_SEQUENTIAL_ID         
 INNER JOIN   LinkedServer2.BillingDB.dbo.tblBuyer b ONba.BuyerId = b.BuyerId   INNERJOIN
                        LinkedServer2.BillingDB.dbo.tblRecipient r ON b.BuyerId = r.BuyerId
 WHERE     o.DATE_ORDERED >= 1199176134783 AND (o.SB_EXPORTED = 1) AND(sc.EMAIL <> '') AND(bc.EMAIL <> '')
 AND (RTRIM(LTRIM(sc.EMAIL))<> RTRIM(LTRIM(r.CoppaEmail))OR RTRIM(LTRIM(bc.EMAIL))<> RTRIM(LTRIM(b.Email)))
 ORDER BY o.DATE_ORDERED DESC

By capturing BatchComplete on the local server and RPC Complete on the two remote servers, with SQL Server Profiler, you can see that each server is negatively impacted by high cpu time, high durations and the significant number of reads on all three servers.

Here is a print screen of the local server.

Here is a print screen of the first Linked Server (LinkedServer1).

Here is a print screen of the second Linked Server (LinkedServer2).

One option, to reduce overhead, is to pull the dataset from one or both of the queries and place them into a temp table and perform the joins locally. In this demonstration, I place the data from LinkedServer2 into a temporary table, which suggificantly reduced duration, because the local temp table data could be compared to the remote data, thus creating only one remote procedure connection.

SELECT     ba.InfoValue, b.Email, r.CoppaEmail, b.CREATED_TS
INTO#tmp
FROM         LinkedServer2.BillingDB.dbo.tblBuyerAdditionalInfo ba INNER JOIN
             LinkedServer2.BillingDB.dbo.tblBuyer b ON ba.BuyerId = b.BuyerId INNER JOIN   LinkedServer2.BillingDB.dbo.tblRecipient r ON b.BuyerId = r.BuyerId
WHERE    (b.CREATED_TS >= '1/1/2008')
SELECT     mb.CREATED_TS, sc.FIRST_NAME, sc.LAST_NAME, sc.EMAIL AS sh_email, bc.EMAIL AS bem, cu.EMAIL AS cust_email, sc.CONTACT_ID, od.ORDERS_ID, bc.FIRST_NAME, bc.LAST_NAME, o.SB_SEQUENTIAL_ID, o.MOZART_EXPORTED, o.SB_EXPORTED, o.MOZART_SEQUENTIAL_ID, bc.CONTACT_ID AS bcid
FROM         LinkedServer1.CustomerDB.dbo.CONTACT sc INNER JOIN                     LinkedServer1.CustomerDB.dbo.ORDER_DETAIL od ON sc.CONTACT_ID = od.SHIP_TO_CONTACT_ID INNER JOIN
                      LinkedServer1.CustomerDB.dbo.ORDERS o ON od.ORDERS_ID = o.ORDERS_ID INNER JOIN
                      LinkedServer1.CustomerDB.dbo.CUSTOMER cu ON o.CUSTOMER_ID = cu.CUSTOMER_ID INNER JOIN                  LinkedServer1.CustomerDB.dbo.CONTACT bc ON cu.BILL_CONTACT_ID = bc.CONTACT_ID AND RTRIM(LTRIM(sc.EMAIL))<> RTRIM(LTRIM(bc.EMAIL))
INNERJOIN #tmp mb ON mb.InfoValue = o.SB_SEQUENTIAL_ID
WHERE     o.DATE_ORDERED >= 1199176134783 AND (o.SB_EXPORTED = 1) AND(sc.EMAIL <>'')AND (bc.EMAIL <> '') AND(RTRIM(LTRIM(sc.EMAIL))<> RTRIM(LTRIM(mb.CoppaEmail))OR RTRIM(LTRIM(bc.EMAIL))<> RTRIM(LTRIM(mb.Email)))
ORDERBY o.DATE_ORDERED DESC

By monitoring the events in SQL Server Profiler once again, we can see that using a temp table reduces the CPU time, reads, and durations significantly compared to the previous query. Here we see a print screen of the local server.

and here is the print screen of LinkedServer1.

And the print screen of LinkedServer2.

To improve performance even more, we could copy one or both databases to the local server by a nightly process or by replication. In this demonstration, I copied the database referenced by LinkedServer1 to the local server. This eliminates the need for LinkedServer1’s remote connection.

Once again, use SQL Server Profiler to capture the events on the local and remote linked servers. Since LinkedServer1’s database is now local, we only need to monitor the local server and LinkedServer2. As demonstrated, the amount of time used on the local server is now down to 10 seconds, which is a significant performance gain from the first two versions of the query. Now that the query is local, we could further speed up the query by adding indexes. Here is the print screen of the local server.

Print Screen of Linked Server2

Conclusion

Linked Servers are useful for retrieving data from remote locations, but can often be the source of poor performance for the servers involved. By using temp tables or moving databases locally, performance can be significantly enhanced. At a pervious company, we found that we were constantly using linked servers to retrieve data from databases on other servers, we found that it was much faster use transactional replication to copy the tables locally than to retrieve data from a linked server. In another situation, we were linking to servers on different domains, we found that it was much faster to pull the data nightly and run the query locally. Linked Servers in queries do have there place in many situations. Sometimes, it is not worth the work effort to copy down tables, databases or even create temp tables in all situations. However, if you start to see performance problems with queries that utilize linked servers, you may want to look for other options.