Home
  •  

Blocking Sessions

Microsoft.SQLServer.2016.DBEngine.BlockedSPIDsMonitor (UnitMonitor)

Monitors blocked sessions for a SQL instance. Note: This monitor is disabled by default. Please use overrides to enable it when necessary.

Knowledge Base article:

Summary

The Blocking Sessions Monitor detects if a blocking situation exists.

Blocking occurs when one session holds a lock on a specific resource and a second session attempts to acquire a conflicting lock type on the same resource. Blocking is an unavoidable characteristic of any relational database management system (RDBMS) with lock-based concurrency. However, too much blocking can cause performance issues.

Causes

When locking and blocking increase to the point where there is a considerable impact on system performance, it is usually due to one of the following reasons:

Resolutions

Alternatively if blocked sessions are not a concern for the database:

Overrideable Parameters

Name

Description

Default Value

Alert Priority

Defines Alert Priority.

High

Alert Severity

Defines Alert Severity.

Error

Enabled

Enables or disables the workflow.

No

Generates Alerts

Defines whether the workflow generates an Alert.

Yes

Interval (seconds)

The recurring interval of time in seconds in which to run the workflow.

300

Number of blocked sessions

The maximum allowed number of blocked sessions.

1

Synchronization Time

The synchronization time specified by using a 24-hour format. May be omitted.

 

Timeout (seconds)

Specifies the time the workflow is allowed to run before being closed and marked as failed.

300

Wait Time (minutes)

The minimum process execution duration before considering it for Blocked SPIDs analysis.

1

Element properties:

TargetMicrosoft.SQLServer.2016.DBEngine
Parent MonitorSystem.Health.PerformanceState
CategoryPerformanceHealth
EnabledFalse
Alert GenerateTrue
Alert SeverityError
Alert PriorityHigh
Alert Auto ResolveTrue
Monitor TypeMicrosoft.SQLServer.2016.DBEngine.BlockedSPIDs
RemotableTrue
AccessibilityPublic
Alert Message
MSSQL 2016: Blocked Session(s)
The session/s {0} in SQL instance "{1}" on computer "{2}" is/are blocked. See "Alert Context" tab for more details.
RunAsDefault

Source Code:

<UnitMonitor ID="Microsoft.SQLServer.2016.DBEngine.BlockedSPIDsMonitor" Accessibility="Public" Enabled="false" Target="SQL2016Core!Microsoft.SQLServer.2016.DBEngine" ParentMonitorID="SystemHealth!System.Health.PerformanceState" Remotable="true" Priority="Normal" TypeID="Microsoft.SQLServer.2016.DBEngine.BlockedSPIDs" ConfirmDelivery="false">

  <Category>PerformanceHealth</Category>

  <AlertSettings AlertMessage="Microsoft.SQLServer.2016.DBEngine.BlockedSPIDsMonitor.AlertMessage">

    <AlertOnState>Error</AlertOnState>

    <AutoResolve>true</AutoResolve>

    <AlertPriority>High</AlertPriority>

    <AlertSeverity>Error</AlertSeverity>

    <AlertParameters>

      <AlertParameter1>$Data/Context/Property[@Name='BlockedSPIDList']$</AlertParameter1>

      <AlertParameter2>$Target/Property[Type="SQL2016Core!Microsoft.SQLServer.2016.ServerRole"]/InstanceName$</AlertParameter2>

      <AlertParameter3>$Target/Host/Property[Type="Windows!Microsoft.Windows.Computer"]/NetworkName$</AlertParameter3>

    </AlertParameters>

  </AlertSettings>

  <OperationalStates>

    <OperationalState ID="Blocked" MonitorTypeStateID="Blocked" HealthState="Error"/>

    <OperationalState ID="NotBlocked" MonitorTypeStateID="NotBlocked" HealthState="Success"/>

  </OperationalStates>

  <Configuration>

    <ConnectionString>$Target/Property[Type="SQL2016Core!Microsoft.SQLServer.2016.DBEngine"]/ConnectionString$</ConnectionString>

    <IntervalSeconds>300</IntervalSeconds>

    <SyncTime/>

    <WaitMinutes>1</WaitMinutes>

    <NumberBlockedSPIDs>1</NumberBlockedSPIDs>

    <TimeoutSeconds>300</TimeoutSeconds>

    <InstanceName>$Target/Property[Type="SQL2016Core!Microsoft.SQLServer.2016.ServerRole"]/InstanceName$</InstanceName>

    <ComputerName>$Target/Host/Property[Type="Windows!Microsoft.Windows.Computer"]/NetworkName$</ComputerName>

  </Configuration>

</UnitMonitor>