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
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
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?
Query Plan: https://i.stack.imgur.com/muSUR.png
IO Stats: https://i.stack.imgur.com/VVoLf.png
Query Columns: https://i.stack.imgur.com/8tzWk.png
Top Operations: https://i.stack.imgur.com/bAOh5.png
Plan Tree: https://i.stack.imgur.com/3AWBk.png