Link to home
Start Free TrialLog in
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?

ASKER CERTIFIED SOLUTION
Avatar of lcohan
lcohan
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
Avatar of Lapchien

ASKER

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.