The queries in question are here: http://pastie.org/4833351

Run individually, each of these queries runs through (albeit slow).

Run as a group, it just sits there, showing either OLEDB waits on the local box or in a SUSPENDED state on the linked server (wait type: ASYNC_NETWORK_IO).

The query finished (after 44 mins) with the following errors:

OLE DB provider "SQLNCLI10" for linked server "[MyServer]" returned message "Protocol error in TDS stream".

OLE DB provider "SQLNCLI10" for linked server "MyServer" returned message "Query timeout expired".

Msg 7330, Level 16, State 2, Line 127 Cannot fetch a row from OLE DB provider "SQLNCLI10" for linked server "MyServer".

Can anyone here point me in the direction of how I can both diagnose and fix this?

  • ASYNC_NETWORK_IO means the sender is waiting for data to finish sending across the network. Linked server queries typically require the entire remote dataset be transferred to the querying server prior to the actual query executing. – Max Vernon Sep 28 '12 at 5:11
  • You may want to ensure the query is looking at a view on the remote server that is limiting the amount of data returned as much as possible. You may also want to ensure the fields you are selecting are limited to the most essential fields. Certainly doing SELECT * FROM [remote-table] is going to be much slower than SELECT Field1, Field2 FROM [remote-table] if the remote contains a lot of fields or fields with large amounts of data such as MEMO or BLOB fields. – Max Vernon Sep 28 '12 at 5:15

Pulling data through a linked server is unlikely to be quick especially if you are joining remote tables to local tables. (Just use Profiler to watch what your local server sends to the remote server and I think that you'll be convinced.) Linked Servers are convenient and often "good enough", but not when you start blowing through your batch processing windows.

I have seen overall processing go faster by pulling the remote data into local (temporary or permanent) tables and then "doing the joins" locally to figure out what to put into the production tables.

If you have a large amount of data, I suggest looking into using a SSIS package or bcp and probably bringing the data into a local staging table first and converting the code to run off of local tables. This might involve a lot of surgery to your jobs since you need to have a job step (or steps) to run bcp code and/or packages.

If you are using SQL 2008 or better, MERGE should be available and it could provide more efficient operations. I am unsure if MERGE works with tables on a linked server and, if it does, performance may not be much better than what you have. So, you are still stuck getting the data onto the local server.

Also, you could look into partition switching, but I think would be the most costly thing to do in terms of time spent on getting things to work. You could view it as a learning experience.

Alternatively, you might consider increasing the query timeout, which is a server-level setting on the local server. That might help with the timeout error (until your data volume increases, then you would need to tweak it again), but I doubt that it will help with the TDS error problem.

If you want to minimize the length of time that your production tables are locked (due to the INSERT, not the SELECT with nolock), you would want to get all of the data from the ETL system onto the local box first, then go and insert it all. You want to avoid pulling data for one table, inserting that, pulling the data for the next table, inserting that, etc. (I'm presuming that you are doing all of this in a declared transaction that I'm not seeing.)

| improve this answer | |

Looking at your queries I assume the purpose of your queries is to keep the local DB tables in sync with the records in the remote DB by inserting the records that do not currently exist in the local DB.

SSIS would be more suited and likely to be a quicker way of doing this. If you wish to have an exact carbon copy of the tables from your remote DB within your local DB you could also look into Replication to keep them in sync.

| improve this answer | |

Just to put it out there as an alternative, "Service Broker" is really fast and allows you to communicate with other nodes using certificates. It has a learning curve but it's one of the fastest and most reliable messaging/queueing systems available.

I would also look into your network issues to see if they can be resolved. Do you know how many hops you're going through and what the bottleneck is? Can you directly query that server for a large amount of data and get it fast w/o acquiring high network_io waits?

| improve this answer | |

Using an ETL tool such as SSIS would be my preferred method of importing your data. However, if you must use Linked Servers, then I would suggest pulling the data from the Linked Server first and storing it in a temp table or another staging table.

FROM [remoteServer].[remoteDBName].[dbo].[productExtended_accessory]

INSERT INTO [LocalDBName].[dbo].[productExtended_accessory]
LEFT OUTER JOIN [LocalDBName].[dbo].[productExtended_accessory] pePROD ON peETL.id = pePROD.id


By pulling all (or some) of the data over and then doing the join locally, you will have better results. Doing a join over a Linked Server can cause unpredictable results.

If you can add a where clause to the initial data pull from the Linked Server, then you can improve the performance even more by only pulling some of the data over.

| improve this answer | |

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.