Where should i focus to fix this query? (network... Query Design) need help

We have a datacenter that connects to another agency's datacenter.  It is connected by fiber and the throughput is substantial.   We have a query that uses linked servers (1 server at each datacenter) and we often times have problems with this query.  this query is the dataset for a highly used ssrs report.     I need help pinning down what the problem is.  The network team has run a connectivity test between the two sites and is telling me the circuit is good.  The reason i mention this is because the sql error that occurs references network problems.

Screenshot of error is attached.  Networkerror.jpg

I ran the query ten times in a row and watched the activity monitor.  it normally takes between 20 and 50 seconds to return the results of the query through management studio(ssms).  1 of those ten times resulted in the aforementioned network error.

I am trying to discern if the problem is related to the network, the design of the query, or not enough server resources for the sql servers in the mix of producting the results.
the activity monitor.jpg shows the activity monitor for this query (highlighed in blue).

My question is this.  Is the network error provided by sql server reliable?  Anything else on the design side that i should address?

Thanks guys

the query itself is below
declare @startdate datetime = dateadd(d,-1,getdate()), @enddate datetime = getdate()

SELECT I.Master_Incident_Number as 'Incident #'
      , I.Id
      , I.Address
      , I.Response_date as 'Date'
      , I.Response_Area
      , I.Problem
      , IsNull(SUBSTRING((SELECT TOP 1 L.Comment FROM Linked ServerCleansedforExpertsExchange L WITH(NOLOCK)
            WHERE L.Master_Incident_ID = I.ID and L.Activity = 'Initial Assignment'),CHARINDEX(': ',(SELECT TOP 1 L.Comment FROM Linked ServerCleansedforExpertsExchange L
            WHERE L.Master_Incident_ID = I.ID and L.Activity = 'Initial Assignment'))+2,LEN((SELECT TOP 1 L.Comment FROM Linked ServerCleansedforExpertsExchangeL
            WHERE L.Master_Incident_ID = I.ID and L.Activity = 'Initial Assignment'))),'No activity') as 'Recommended'
      , (SELECT TOP 1 L.Date_Time from Linked ServerCleansedforExpertsExchange L WITH(NOLOCK)
            WHERE L.Master_Incident_ID = I.Id and L.Activity = 'UserAction' and L.Comment = 'User clicked Initial Assign') as 'When_Clicked'
      , STUFF((SELECT ', ' + V.Radio_Name + IIF(V.Elapsed_Enroute_2_Arrival IS NOT NULL,' (' + V.Elapsed_Enroute_2_Arrival + ')','') FROM Linked ServerCleansedforExpertsExchange V WITH(NOLOCK) where V.Master_Incident_ID = I.ID ORDER BY V.Radio_Name FOR XML PATH('')),1,1,'') as 'Responding Unit(s)'
      , ISNULL((SELECT TOP 1 Dispatcher_Init FROM Linked ServerCleansedforExpertsExchange L WITH(NOLOCK) WHERE L.Master_Incident_ID = I.ID and Activity = 'Disp' ORDER BY Date_Time), 'No activity') as 'Dispatcher'
, (SELECT ID From LocalServerCleansedforExpertsExchange U WITH(NOLOCK) Where U.incId = I.ID) as 'LogId'
FROM Linked ServerCleansedforExpertsExchange I WITH(NOLOCK)
--WHERE I.Response_Plan <> '' and Response_Date > DateAdd(day,-1,GETDATE()) and I.Jurisdiction like 'Fire%'
WHERE I.Response_Plan <> '' and Response_Date BETWEEN @StartDate and @EndDate and I.Jurisdiction like 'Cleansed for Experts Exchange'
ORDER BY Response_Date DESC
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ares KurkluSoftware EngineerCommented:
Is it possible that there might be a DNS issue?
Mark WillsTopic AdvisorCommented:
You're doing a fair few inline subquerys which can be very expensive in terms of resources.

waitstates could be generating a timeout.

I would focus on the query first to see if it can be streamlined / optimised

Let me have a look at the SQL and I'll get back to you

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Vitor MontalvãoMSSQL Senior EngineerCommented:
Do you have a Linked Server name with an IP number?
Mark WillsTopic AdvisorCommented:
Apologies, I said I would get back to you and havent....

I see a couple of declaritives, so assume it is part of a stored procedure. Means we can do more...

It would be very tempting to do a SELECT ... INTO #TMP_WORK_FILE FROM [linkedserver]  
Or an INSERT  @TMP_WORK_FILE SELECT ... FROM [linkedserver]

And then use the TMP_WORK_FILE to drive the query.

Even if you do just enough to grab the row where you have the complicated subquery / charindexes

I would go a step further and try to resolve the other inline subqueries via a join, or because of the top 1, maybe a cross apply.

Same data, but grabbing it at once instead of row by row with the inline subqueries.

Depending on how many ID's per Master_Incident_ID, might even be worthwhile grabbing those as part of the grab for TMP_WORK_FILE
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.