2

We’re running into issues with unresolved deadlocks on the most recent version of SQL-Server 2017, has anybody seen a problem like this before?

Detailed explanation: We migrated our database from Sybase-ASE to SQL Server 2017 (RTM-CU23) two weeks ago. In that time there have been a number of deadlocks reported in the System Health Events, three of which SQL Server was unable to kill, resulting in our business processes being blocked. We had to kill the SPID by hand, so that processing was able to continue. The queries themselves are rather complex and return a large resultset, because of this SQL-Server runs several threads in parallel (we are not limiting “maxdop”). The queries causing the deadlock were not the same every time, but they always included parallelism and tables with triggers.

During an unresolved deadlock, the “xml_deadlock_report” event, which is generated every 100ms, shows the “waittime” of most stuck threads increasing, otherwise the status and content does not change. One of the stuck threads however has a waittime which does not increase but varies between 96 and 105ms. In the “xml_deadlock_report” the process owning this thread is reported as the “victim” (shown in the picture below).The problem is that this process does not actually get killed.

enter image description here

This problem looks related to the recent KB Entries KB4338715 and KB4089473, but as noted above we're running the latest version. We’re looking for any ideas or hints that could help us.

Improve this question
2
  • Adding the offending queries and their exec plans (pastetheplan.com) will be helpful for folks to go deeper on your problem. But knowing "we are not limiting “maxdop”" and "they always included parallelism and tables with triggers" I predict you're going to have to reassess both your lack of MAXDOP and use of triggers. The latter of which are usually frowned upon because they can easily lead to situations like this. You may also find SET DEADLOCK_PRIORITY useful
    – LowlyDBA - John M
    Apr 28, 2021 at 14:14
  • I suspect the reason the process was not killed immediately may be to do with large rollbacks. Without table and index definitions, and the queries involved, it's impossible to diagnose a deadlock. If there are many different queries involved then you should look for patterns where the same indexes are involved, or the same sequence of access
    – Charlieface
    Apr 29, 2021 at 0:21

0

Reset to default