0

A client have a data warehouse server with a linked server set-up. The data warehouse server is SQL Server 2014, while the linked server is a SQL Server, but version unknown.

Those two servers was previously physically at the same location, but a while ago the linked server was moved across the Atlantic. The performance was drastically hurt.

While I do understand that the latency is affected, I did not expect the throughput to be hurt as much. I'm talking about 20 minutes before and 15 hours after the relocation.

Monitoring wait stats shows pretty much async_network_io only.

I've tried to research how a query is processed across linked servers, but have not been able to find something useful. It appears to me that it's not as simple as the data warehouse sending a query to the linked server and the linked server starts to feed data back to the data warehouse. I do get the impression that it is continuous messages sent back and forth and therefor the latency plays a huge part.

The queries are simple selects from single tables.

Setting this up in SSIS improves performance to an acceptable level, but will take a lot of time to set up.

Anyone knows what's going on here?

UPDATE - Further details requested from David S

A typical table that is selected from the linked server and inserted to the local server is ~5 000 000 rows @ 4 GB

The query runs on a view:

CREATE VIEW [dbo].[Order_line]
AS
SELECT [ordno]
  ,[divcode]
  ,dbo.UTCOFFSET([regdate]) as regdate
  ...
  ,[o08rqtyb]
  ,[marowver]
FROM [LinkedServer].[Db1].[dbo].[O08T1]

The query to insert and update rows looks like this (Contained in a SP)

UPDATE
    T1
SET
    T1.[ordno] = T2.[ordno],
    T1.[divcode] = T2.[divcode],
    ...
    T1.[marowver] = T2.[marowver],
    T1.[updateTime] = GETDATE()
FROM
    [t_Order_line] T1 
JOIN [Order_line] T2 ON
    T1.shorto08 = T2.shorto08 and
    T1.marowver < T2.marowver

INSERT INTO [ASTRO_Auburn].[dbo].[t_Order_line]
       ([ordno]
       ,[divcode]
       ...
       ,[marowver]
       ,[insertTime])
SELECT T1.[ordno]
    ,T1.[divcode]
    ...
    ,T1.[marowver]
    ,GETDATE()
FROM [Order_line] T1 WITH (NOLOCK)
LEFT JOIN [t_Order_line] T2 ON
    T1.shorto08 = t2.shorto08
WHERE t2.[ordno] IS NULL

I know that there are questionable decisions taken when this was set-up, but it is the legacy from another time.

Fetching the query plan that is executed on the remote server only shows a Table scan. Possibly that scan is executed twice? But that's not the major issue here.

  • 1
    If you're looking for more information on how linked server queries work, start here: sqlbits.com/Sessions/Event10/Distributed_Query_Deep_Dive – Erik Darling May 29 '17 at 12:57
  • @sp_BlitzErik: That is a great resource. It will keep me occupied for a while. Thank you! – Anders May 30 '17 at 14:10
  • More than likely the query is so bad that SQL Server is bringing over the entire table to the source server and then doing the join eliminations. On a local network, that took 20 minutes for 4GB... but closer to 15 hours with your current network throughput. I would try putting appropriate indexes on the table in the linked server or rewrite the query so that you can "pre-fetch" the rows you need from the linked server and then join that on. Or use some alternative means such as SSIS or log shipping/Availability Groups to keep a local copy of the table in sync. – Jonathan Fite May 30 '17 at 14:47
  • query plan that is executed on the remote server only shows a Table scan - include the query plan in your question, perhaps via something like brentozar.com/pastetheplan. When asking for help, always include all the information you have rather than leaving the people trying to help to guess. – David Spillett May 31 '17 at 10:00
  • @DavidSpillett, point taken. Unfortunately I don't have elevated access to the linked server myself. This was noticed at an earlier Troubleshooting session. I should have remembered to mention it from the start though. – Anders May 31 '17 at 11:30
1

The queries are simple selects from single tables.

There are a number of details that you need to include that may have an effect here. Are they many small resultsets (a few rows or small rows) or small numbers of large results (many rows and/or large columns like BLOBs)? If it is a mix can you separate them out and test the performance separately?

How are you making SELECTS to the remote end? Direct inserts into a local table with something like INSERT localtable SELECT stuff FROM server.db.schema.table or are you reading the results into your own code for further processing before INSERT? If the latter than you might need to look into which cursor options you (or the framework you are calling through) are using and see if there are less latency sensitive options that are applicable (assuming the framework you are using allows you this level of control). If the options used mean that each row is request separately then latency will be far more of an issue then if they allow the rows to be read over the network in bulk and navigated locally.

Setting this up in SSIS improves performance to an acceptable level

If you are using exactly the same statements to retrieve the data in SSIS as your previous method then that could imply one of three things, or a mix of them:

  1. The cursor options as mentioned above (SSIS is more likely to be favour latency friendly options which may in other circumstances be sub-optimal, because it is more often used to work with more widely distributed servers)
  2. Connection pooling which your current method might not be making as much advantage of (again, here we need more details to do more than guess). SSIS will use the same connection rather than renegotiating for each query, your other process may not be doing this.
  3. Possibly concurrency: is your workflow in SSIS allowing it to submit several queries at the same time, so it can submit new queries to the remote end without first having to wait for the previous ones to complete, where the workflow in your current process is entirely sequential?
  • thank you for your answer. I have updated the question with examples of the code. – Anders May 30 '17 at 14:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.