Chat now with support
Chat with Support

Spotlight on SQL Server Enterprise 11.7 - Management Pack for SCOM User Guide

Locks - Blocked Processes Alarm

The Locks - Blocked Processes alarm is raised when a block persists for longer than the time specified in the threshold. The default is 120 seconds.

Blocking occurs when there is at least one SQL Server 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.

Note: Many applications do not set a timeout and will wait forever for locks to be freed.

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 Blocking page on the SQL Server | SQL Activity Drilldown 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 Sessions page on the SQL Server | SQL Activity Drilldown 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 Locks page on the SQL Server | SQL Activity Drilldown to view all locks in the system. This will show you all SQL Server locks currently granted or requested.

    You can kill any session and free up any locks it holds. Select the session and click Kill session.

Configuration

Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating