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. enter image description here

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


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

  • 2
    I always use "Collation Compatible = true" unless they really aren't, but since you say the degradation happened suddenly, I doubt this is a config issue. Commented Aug 8, 2012 at 16:31
  • 2
    Whoa, four distinct sorts, that seems high. :-) Commented Aug 8, 2012 at 18:44
  • @AaronBertrand you just saved my day with the "Collation Compatible = true" recommendation. Thank you!
    – deebs
    Commented Nov 1, 2018 at 19:34

2 Answers 2


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.


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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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