Hi, following a code release we have seen random timeout errors. I'm not sure whats causing the system to slow down to the point that a timeout happens so set a SQL profiler running and found that on 2 traces before the timeout occurs there is a deadlock thing recorded...
SET DEADLOCK_PRIORITY -10
Does this mean that a deadlock is being resolved or what? im a c# dev so this really isnt my bag. any help would be much appreciated.
December 1, 2020 at 4:37 pm
That means that the task being run will be cancelled if it is involved in a deadlock. It doesn't mean that a deadlock will occur. -10 is the lowest priority possible, so, by default, every other task will be considered more important.
By default, deadlock priority is 0. If two tasks get into a deadlock, SQL uses its own stats -- which comes down to SQL determining "which task being cancelled would require less work" -- to determine which one to cancel.
But, if one task has a lower priority than the other one(s), SQL will cancel that task, no matter what the stats are for each query.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
December 1, 2020 at 4:51 pm
Thats a really nice, easy to understand writeup. thanks a lot.
Do you have any recommendations as to how I would go about diagnosing what could be causing the timeout?
Look in the SQL error log, you might be able to at least some more details about what task timed out, though admittedly not a lot of details.
Typically timeouts are caused by blocking. Blocking is normal and expected in SQL Server (or any other relational dbms), but excessive blocking is a problem.
Also, make sure no database backup(s) are occurring during normal database activity. Backups can cause serious slowdowns and contentions in any SQL database.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
December 2, 2020 at 8:56 am
Wonderful @ssc guru. After ruling out deadlocks, checking error logs and backup schedule I actually managed to find a sql agent job that was running some awful queries. ????
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply