Deadlocks and excessive blocking is the bane of many development teams and DBAs. They are often at the root of performance problems. Understanding each of these problems and how to solve the root cause can be difficult. I find that the terms are used interchangeably and frequently mixed. Understanding the difference, and diagnosing the issue is crucial to resolving these problems. Both happen due to incompatible locks being held or requested on tables, but they differ in their root cause. Some troubleshooting methods work for both problems, but deadlocks generally require logic changes to fix permanently and consistently.
This post is aimed at giving a general background on blocking and deadlocks. It also shows some basic methods to find these problems and general ways to fix them. I’m not going into extreme detail. This is just an introduction.

SQL Server Managed Instance Diagnostics

The great thing about SQL Server Managed Instance (SQL MI) is that you don’t need to perform any maintenance or troubleshooting on the OS or SQL installation. In theory. If you’ve used SQL MI, you might find that this isn’t always the case. If you have issues, you’ll find that you have limited troubleshooting options on the server and your options for running scripts are constrained. Microsoft support doesn’t have unfettered access either. They have hard boundaries on what can be accessed on your servers and they need to escalate quite extensively to other internal teams when examining certain problems.

DBA Tasks in Azure DB

It was a big surprise to me when I learned that Azure SQL Database doesn’t update statistics or indexes automatically. The option to automatically update statistics exists (e.g., ALTER DATABASE [WideWorldImporters] SET AUTO_UPDATE_STATISTICS ON), and is on by default, but it does not give the same control as updating them manually with the ‘UPDATE STATISTICS’ command. When Azure DB was first pitched to us, on an internal project, one of the big selling points was “no maintenance required”. This wasn’t a statement from Microsoft or even from the documentation. It was clearly a marketing talking-point that leaked into the technical discussions. It’s partially true. Some things are automatic, but there are a few things you still need to maintain or at least monitor yourself. The big shocker, as you already know from the first sentence above, is that indexes aren’t automatically rebuilt for you and statistics aren’t maintained beyond the database configuration item.