Monitors blocked sessions for a SQL instance. Note: This monitor is disabled by default. Please use overrides to enable it when necessary.
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.
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
Kill the session at the head of the blocking chain
Shorten transaction times
Create proper indexes
Use locking hints. See SQL Server Books 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
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 |
Target | Microsoft.SQLServer.2016.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.2016.DBEngine.BlockedSPIDs | ||
Remotable | True | ||
Accessibility | Public | ||
Alert Message |
| ||
RunAs | Default |
<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>