Blocking Sessions

Microsoft.SQLServer.Linux.Monitor.DBEngine.BlockingSessions (UnitMonitor)

Monitors blocked sessions for a SQL instance on Linux. Note that this monitor is disabled by default. 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:

A session holds locks on a set of resources and never releases them. Usually caused by:
- Cancelled queries that are not rolled back
- Orphaned transactions

A session (identified by a session_id or “SPID”) holds locks on a set of resources for an extended period of time before releasing them. Usually caused by:
- Long-running transactions
- Lack of appropriate indexes
- Inappropriate use of locking hints
- Other issues related to poor application design

Resolutions

Kill the session at the head of the blocking chain
Shorten transaction times
Create proper indexes
Use locking hints. See SQL Server Docs Online
Use row versioning-based Isolation Levels
Configure SQL Server settings (memory settings, lock timeouts, etc.)
Change the thresholds on the monitor for this specific database or all databases

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

Disable the monitor for this specific database or all databases

Overridable 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.	200
Timeout for query execution (seconds)	The workflow will fail and register an event, if the query execution takes longer than the specified period.	60
Timeout for database connection (seconds)	The workflow will fail and register an event, if it cannot access the database during the specified period.	15
Wait Time (minutes)	The minimum process execution duration before considering it for Blocked SPIDs analysis.	1

Element properties:

Target

Microsoft.SQLServer.Linux.DBEngine

Parent Monitor

System.Health.PerformanceState

Category

PerformanceHealth

Enabled

False

Alert Generate

True

Alert Severity

Error

Alert Priority

High

Alert Auto Resolve

True

Monitor Type

Microsoft.SQLServer.Linux.MonitorType.DBEngine.BlockingSessions

Remotable

True

Accessibility

Public

Alert Message

MSSQL on Linux: Blocked Session(s)

The following session(s) on SQL Server instance "{1}", computer "{0}" are blocked: {2}. See the "Alert Context" tab for more details.

RunAs

Default

Source Code:

<UnitMonitor ID="Microsoft.SQLServer.Linux.Monitor.DBEngine.BlockingSessions" Accessibility="Public" Enabled="false" Target="SqlDiscL!Microsoft.SQLServer.Linux.DBEngine" ParentMonitorID="Health!System.Health.PerformanceState" Remotable="true" Priority="Normal" TypeID="Microsoft.SQLServer.Linux.MonitorType.DBEngine.BlockingSessions" ConfirmDelivery="false">

  <Category>PerformanceHealth</Category>

  <AlertSettings AlertMessage="Microsoft.SQLServer.Linux.Monitor.DBEngine.BlockingSessions.AlertMessage">

    <AlertOnState>Error</AlertOnState>

    <AutoResolve>true</AutoResolve>

    <AlertPriority>High</AlertPriority>

    <AlertSeverity>Error</AlertSeverity>

    <AlertParameters>

      <AlertParameter1>$Target/Property[Type="SqlCoreLib!Microsoft.SQLServer.Core.DBEngine"]/MachineName$</AlertParameter1>

      <AlertParameter2>$Target/Property[Type="SqlCoreLib!Microsoft.SQLServer.Core.DBEngine"]/InstanceName$</AlertParameter2>

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

    </AlertParameters>

  </AlertSettings>

  <OperationalStates>

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

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

  </OperationalStates>

  <Configuration>

    <MachineName>$Target/Property[Type="SqlCoreLib!Microsoft.SQLServer.Core.DBEngine"]/MachineName$</MachineName>

    <NetbiosComputerName>$Target/Property[Type="SqlCoreLib!Microsoft.SQLServer.Core.DBEngine"]/NetbiosComputerName$</NetbiosComputerName>

    <InstanceName>$Target/Property[Type="SqlCoreLib!Microsoft.SQLServer.Core.DBEngine"]/InstanceName$</InstanceName>

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

    <InstanceVersion>$Target/Property[Type="SqlCoreLib!Microsoft.SQLServer.Core.DBEngine"]/Version$</InstanceVersion>

    <InstanceEdition>$Target/Property[Type="SqlCoreLib!Microsoft.SQLServer.Core.DBEngine"]/Edition$</InstanceEdition>

    <IntervalSeconds>300</IntervalSeconds>

    <SyncTime/>

    <WaitMinutes>1</WaitMinutes>

    <NumberBlockedSPIDs>1</NumberBlockedSPIDs>

    <TimeoutSeconds>200</TimeoutSeconds>

    <SqlExecTimeoutSeconds>60</SqlExecTimeoutSeconds>

    <SqlTimeoutSeconds>15</SqlTimeoutSeconds>

  </Configuration>

</UnitMonitor>