Blocking occurs when there is at least one Azure SQL Managed Instance session that is waiting on a lock held by another session. The waiting user is said to be ‘blocked’ by the one holding the lock, and will wait until either the blocking user commits/rolls back (and therefore frees up the resource being waited on), or the blocked user’s application timeout expires, in which case the blocked command will be canceled.
Excessive blocking can be a major cause of poor application performance, as the user of an application often does not realize that they are waiting on a lock held by another user. From their point of view, it often seems like their application has stopped responding.
When the alarm is raised look at:
-
The SQL Server | SQL Activity drilldown | Blocking page to see who is blocking whom, and what resources are involved (for example, database and table names etc). In the case of multiple blocks where there are blocked sessions that are also blocking others, this page will show you who is at the head of the chain and is, therefore, probably the root cause of all the blocking. This page also shows how many sessions were blocked over time.
You can kill any session and free up any locks it holds. Select the session and click Kill session.
-
The SQL Server | SQL Activity drilldown | Sessions page to view the most recent SQL for the sessions involved in the blocking. This can help track down sub-optimal SQL that may contribute to the locking problem.
You can kill any session and free up any locks it holds. Select the session and click Kill session.
-
The SQL Server | SQL Activity drilldown | Locks page to view all locks in the system. This will show you all Azure SQL Managed Instance locks currently granted or requested.
You can kill any session and free up any locks it holds. Select the session and click Kill session.
Using a Spotlight Client you can configure this alarm to ignore certain values. You can configure different alarm severities for specific blocked sessions. The key for this alarm is the SPID of the blocked session. You can see this information on the SQL Server | SQL Activity drilldown | Blocking page.