Monitors blocked sessions for a SQL instance.
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
孤立したトランザクション
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:
実行時間の長いトランザクション
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
See also: “ INF: Understanding and resolving SQL Server blocking problems”
Target | Microsoft.SQLServer.2008.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.2008.DBEngine.BlockedSPIDs | ||
Remotable | True | ||
Accessibility | Public | ||
Alert Message |
| ||
RunAs | Default |
<UnitMonitor ID="Microsoft.SQLServer.2008.DBEngine.BlockedSPIDsMonitor" Accessibility="Public" Enabled="false" Target="SQL2008Core!Microsoft.SQLServer.2008.DBEngine" ParentMonitorID="SystemHealth!System.Health.PerformanceState" Remotable="true" Priority="Normal" TypeID="Microsoft.SQLServer.2008.DBEngine.BlockedSPIDs" ConfirmDelivery="false">
<Category>PerformanceHealth</Category>
<AlertSettings AlertMessage="Microsoft.SQLServer.2008.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="SQL!Microsoft.SQLServer.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="SQL!Microsoft.SQLServer.DBEngine"]/ConnectionString$</ConnectionString>
<IntervalSeconds>300</IntervalSeconds>
<SyncTime/>
<WaitMinutes>1</WaitMinutes>
<NumberBlockedSPIDs>1</NumberBlockedSPIDs>
<TimeoutSeconds>300</TimeoutSeconds>
<InstanceName>$Target/Property[Type="SQL!Microsoft.SQLServer.ServerRole"]/InstanceName$</InstanceName>
<ComputerName>$Target/Host/Property[Type="Windows!Microsoft.Windows.Computer"]/NetworkName$</ComputerName>
</Configuration>
</UnitMonitor>