Avatar of Lapchien
LapchienFlag for United Kingdom of Great Britain and Northern Ireland asked on

SQL 2019 upgrade deadlocks

We recently moved our SQL 2008 R2 database to SQL 2019.  We have left the compatibility level at 2008 whilst we look at the date issue.  We have now started to encounter many more deadlocks than on the old box - despite the new server being more powerful etc.


Is there anything we need to look for or be aware of that could be causing these deadlocks on the new server?

Microsoft SQL Server

Avatar of undefined
Last Comment
Lapchien

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
lcohan

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Dennes Torres

Hi,

Deadlock is more a development problem than a database problem. The fact of the server be "more powerful" as you describe may be part of the reason of the deadlocks: They can be hidden behind slow performance and small amount of access and when you increase the volume they appear.

In order to solve them, you need to identify which parts of the application are causing the deadlock. To do so you should capture the deadlock report using extended events. Once you capture it, add more information to the question here and we will be able to help more.

There is a very common cause of deadlocks: The use of .NET transactionScope class with default configuration (which is serializable). Every instance of this class should always be changed to readCommitted isolation level.

Kind Regards,

Dennes
ASKER
Lapchien

We reindexed everything, which helped, but then when we ran the migration tool there was 1 view with dateadd that does not work in 2019 - once that was amended we upgraded the db level and since then all the deadlocks have gone away and performace has increased.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes