Linked Servers are a mechanism used in SQL Server to retrieve data from remote servers. While they provide convenience, they can also be the cause of query performance issues. In this article, we will explore how linked servers can impact query performance and discuss strategies to improve it.
Recently, a user approached me with a query that was taking an extremely long time to execute. Upon investigation, I discovered that the query was being run on our local production server and involved joining data from two linked servers, LinkedServer1 and LinkedServer2. This meant that a significant amount of data was being pulled across the network to satisfy the query.
By capturing BatchComplete on the local server and RPC Complete on the two remote servers using SQL Server Profiler, it became evident that each server was negatively impacted by high CPU time, long durations, and a significant number of reads. This was affecting the overall performance of the query.
One option to reduce this overhead is to pull the dataset from one or both of the queries and place them into temporary tables. By doing so, we can perform the joins locally, minimizing the need for remote procedure connections. In our demonstration, we moved the data from LinkedServer2 into a temporary table, which significantly reduced the duration of the query.
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))
INNER JOIN #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)))
ORDER BY o.DATE_ORDERED DESC
By monitoring the events in SQL Server Profiler once again, we can observe that using a temporary table reduces CPU time, reads, and durations significantly compared to the previous query.
To further improve performance, we can consider copying one or both databases to the local server through a nightly process or replication. In our demonstration, we copied the database referenced by LinkedServer1 to the local server. This eliminates the need for a remote connection to LinkedServer1, resulting in a significant performance gain.
Once the query is executed locally, we can further optimize it by adding appropriate indexes. This can further enhance the query’s performance.
Linked Servers have their place in many situations, allowing us to retrieve data from remote locations. However, if we start experiencing performance problems with queries that utilize linked servers, it is essential to explore alternative options.
At a previous company, we discovered that using transactional replication to copy tables locally was much faster than retrieving data from a linked server. In another scenario, when linking to servers on different domains, pulling the data nightly and running the query locally proved to be more efficient.
It is important to evaluate the specific requirements and constraints of each situation to determine the most suitable approach.
In conclusion, while Linked Servers provide convenience, they can impact query performance. By utilizing strategies such as temporary tables or moving databases locally, we can significantly enhance performance. If you encounter performance issues with queries involving linked servers, it is worth exploring alternative options to optimize your SQL Server environment.