Sudden Query Performance Degradation On A Linked Server

Question

I have an application that uses a view on a SQL Server 2008 R2 instance. This view actually queries a linked server (SQL Server 2008) that has replicated data from our main ERP database. Yesterday, we began experiencing some timeout on the application that uses this view and we discovered that the view was taking over 1 minute to complete (before this it was a matter of seconds).

Here's what we know:

• Running the query on the linked server directly returns results in a matter of milliseconds.
• Creating a view on the linked server, and then having the view on the first server simply query the view on the linked server restores performance to its original state. (view source), (workaround)
• When running the query on the first server, I notice that there are quite a few OLEDB waits (using sp_WhoIsActive). Because of this, I decided to check the linked server while the query was running (on the first server). I noticed that the primary wait was ASYNC_NETWORK_IO.

Because of this, I am inclined to believe that there is an issue with either the network, or the configuration on my linked server. I am not sure that there is anything wrong with it, but I am posting it too.

What other things should I be looking at to help me to get to the root cause of this?

EDIT

Query Plan: https://i.sstatic.net/muSUR.png

IO Stats: https://i.sstatic.net/VVoLf.png

Query Columns: https://i.sstatic.net/8tzWk.png

Top Operations: https://i.sstatic.net/bAOh5.png

Plan Tree: https://i.sstatic.net/3AWBk.png

Answer 1

If I'm reading the plan and operations correctly, it looks like those nested loops joins with the inner remote operations are the problem.

They were estimated to execute ~750 times apiece, but actually executed almost 6,000 times each. Normally that might not be such an issue, but if network latency is involved with every call, the query becomes extremely sensitive to network conditions. This would certainly explain the ASYNC_NETWORK_IO waits you're seeing.

In any event, I would stick with your workaround -- it's probably better to protect the tables behind a view anyway instead of trying to do all the joins remotely.

Answer 2

Looking at your query, it seems you're only pulling data from one table in your linked server. Have you tried pulling the data you need into a temp table? Then joining to that temp table on your local server? This way SQL Server has all of the statistics and indexes locally to figure out the best way to run your query.

Other ideas:

1. Pre-stage your data - especially from large tables - as much as you can into temp tables to join to as few tables as you can. In the background, SQL Server will cross join all of your data before filtering it.
2. Add indexes where appropriate including on your temp tables.