find what cause a timeout

[sapator]

Hi I'm doing a .net ExecuteNonQueryAsync in my code and I get a timeout from the database on the exception.
Is there a way to detect what caused the exception? Meaning what is blocking the call?
I can't just put a monitor as the call is possibly blocked from early backup update insert jobs that are running for 3-4 hours so it's a shot in the dark if I try to search manually.
Also SQL logs don't reveal anything and I can't stay 4hours early in the monitoring if the error will happen as it happens at random morning hours and not every day.

Thanks.

[Shaggy Hiker]

From the stuff I work on, the first question I would ask is: Is it obvious? It may not be. We write some truly horrific views. Without getting into WHY we write truly horrific views, the simple fact is that if we get timeouts, our response is, "yeah, that query is too much." Basically, we have views of views of views, nested perhaps 6-12 layers deep and bringing in tables from different databases and different servers that may be located in different counties. Those suckers can take a LONG time to complete (by computer standards), so we've been doing things to improve performance because otherwise timeouts were inevitable.

So, timeouts don't necessarily mean that anything has gone wrong, it could just mean that something has taken a horrible amount of time. Index fragmentation could cause that, bad designs could cause that, and so forth. I don't think that a timeout simply because the query was taking too long (though it would have eventually worked) can be detected. Timeouts due to one operation blocking a second operation could be a different story, as SQL can detect that, but it might not be able to detect that condition in a timely fashion.

[sapator]

Thanks.
It's not the query, the query does 2 top(1) updates with 2 inner joins and a left join (that , yeah, maybe but not really as it's indexed) and also has a specific date search that takes a lot of load off.
The query runs in 1 second and I remind that this runs all days without any issue whatsoever and also the after morning hours are a LOT more bloated, if any it should timeout at those hours.
The problem is on a a range from 5 to 10 in the morning that , nothing is happening really just the backup insert update jobs that are interfering. It would be ideal to see what blocked it.
I can raise the timeout of nonlock it but I'm scratching my back of doing more damage this way as something else more important may break or miss.


Edit. I've put a monitor based here: https://www.brentozar.com/archive/20...active-take-2/
Hope i don't block the jobs but OK it's for one day test, I can merge them to run next day if they are blocked...

[TysonLPrice]

Thanks.
It's not the query, the query does 2 top(1) updates with 2 inner joins and a left join (that , yeah, maybe but not really as it's indexed) and also has a specific date search that takes a lot of load off.
The query runs in 1 second and I remind that this runs all days without any issue whatsoever and also the after morning hours are a LOT more bloated, if any it should timeout at those hours.
The problem is on a a range from 5 to 10 in the morning that , nothing is happening really just the backup insert update jobs that are interfering. It would be ideal to see what blocked it.
I can raise the timeout of nonlock it but I'm scratching my back of doing more damage this way as something else more important may break or miss.


Edit. I've put a monitor based here: https://www.brentozar.com/archive/20...active-take-2/
Hope i don't block the jobs but OK it's for one day test, I can merge them to run next day if they are blocked...

Are the queries using "With (NoLock)". That may help with locking.

[Shaggy Hiker]

Is this all done in a transaction? Can it be triggered twice? For example, if process A could start it, then process B comes along and starts it, and it runs in a transaction, then I believe the chance of a deadlock goes way up.

That doesn't help with the actual question, of course, because the timeout would be due to a deadlock, and nothing about that would detect that a deadlock happened. It might be possible to alleviate the problem, though, since the timeout could simply indicate that a deadlock DID happen, so abandon the process and try it again. What might be a better alternative, if the actions can be launched multiple times, is to synclock the SQL operations.

[sapator]

Actually I run sp_whoisactive yesterday and go a log on transaction, cpu usage, locks etc. Unfortunately there was no lock yesterday to be able to search.

On the nolock question, my bad I wrote "I can raise the timeout of nonlock it but I'm scratching my back of doing more damage this way as something else more important may break or miss."
I wanted to write ""I can raise the timeout or nonlock " , so the interpretation was wrong . So nolock would possibly end up on transactions of actually money not be written and me getting arrested

[TysonLPrice]

Actually I run sp_whoisactive yesterday and go a log on transaction, cpu usage, locks etc. Unfortunately there was no lock yesterday to be able to search.

On the nolock question, my bad I wrote "I can raise the timeout of nonlock it but I'm scratching my back of doing more damage this way as something else more important may break or miss."
I wanted to write ""I can raise the timeout or nonlock " , so the interpretation was wrong . So nolock would possibly end up on transactions of actually money not be written and me getting arrested

That is a downside of NoLock. What the NOLOCK hint does is enforce SQL Server's Read Uncommitted isolation level, which allows for what is known as dirty reads. Not a great idea for real-time activity.

[sapator]

Yep.
Unfortunately today no errors again and I'll be on my first batch of vacations from tomorrow, so this will have to wait...