Blocking Session Count

Microsoft.SQLServer.AnalysisServices.Windows.Monitoring.Monitor.TabularDatabase.BlockedSessionsNumber (UnitMonitor)

The monitor alerts when the number of sessions that are blocked longer than the configured 'WaitMinutes' setting exceeds the configured threshold.

Knowledge Base article:

Summary

The monitor alerts if the number of sessions blocked longer than the configured WaitMinutes setting exceeds the configured threshold. Blocking occurs when one session holds a lock on a specific resource, and another session attempts to acquire a conflicting lock type on the same resource. Blocking is an unavoidable characteristic of any database management system with lock-based concurrency. However, too much blocking can cause performance issues; so, the administrator may use this monitor to be aware when this condition lasts for a longer period than the configured 'WaitMinutes' setting for a number of sessions greater than the configured threshold.

Causes

Blocking can occur for numerous reasons, which are expected, but can also occur in scenarios, where it may be ameliorated without any loss of corresponding functionality required by the application consuming the data.

Resolutions

Resolution of blocking issues on a server requires knowledge of workload, requirements, and users, which the administrator must consider when deciding on a correct course of remediation. Remediation of blocking issues might include several potential actions, including but not limited to the following:

External

Configure Server Properties in Analysis Services

TechNet documentation for SSAS Performance Counters

Overridable Parameters

Name

Description

Default Value

Enabled

Enables or disables the workflow.

Yes

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.

900

Number of samples

Indicates how many times a measured value should breach a threshold before the state is changed.

4

Synchronization Time

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

 

Critical Threshold

Health state changes when the number of blocked sessions exceeds the threshold.

10

Timeout (seconds)

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

300

Wait Minutes

Sets the minimum waiting time for the session to be considered by the monitor.

1

Element properties:

TargetMicrosoft.SQLServer.AnalysisServices.Windows.TabularDatabase
Parent MonitorSystem.Health.PerformanceState
CategoryPerformanceHealth
EnabledTrue
Alert GenerateTrue
Alert SeverityError
Alert PriorityNormal
Alert Auto ResolveTrue
Monitor TypeMicrosoft.SQLServer.AnalysisServices.Windows.Monitoring.MonitorType.Database.BlockedSessionsNumber
RemotableTrue
AccessibilityPublic
Alert Message
SSAS: Blocking Session Count exceeded
{1} sessions are blocked longer than {0} min, exceeding the configured threshold for this alert.
RunAsDefault

Source Code:

<UnitMonitor ID="Microsoft.SQLServer.AnalysisServices.Windows.Monitoring.Monitor.TabularDatabase.BlockedSessionsNumber" Target="SQLAS!Microsoft.SQLServer.AnalysisServices.Windows.TabularDatabase" TypeID="Microsoft.SQLServer.AnalysisServices.Windows.Monitoring.MonitorType.Database.BlockedSessionsNumber" ParentMonitorID="SystemHealth!System.Health.PerformanceState" Accessibility="Public" Enabled="true" Remotable="true" Priority="Normal" ConfirmDelivery="false">
<Category>PerformanceHealth</Category>
<AlertSettings AlertMessage="Microsoft.SQLServer.AnalysisServices.Windows.Monitoring.Monitor.TabularDatabase.BlockedSessionsNumber.AlertMessage">
<AlertOnState>Error</AlertOnState>
<AutoResolve>true</AutoResolve>
<AlertPriority>Normal</AlertPriority>
<AlertSeverity>Error</AlertSeverity>
<AlertParameters>
<AlertParameter1>$Data/Context/Property[@Name='WaitTime']$</AlertParameter1>
<AlertParameter2>$Data/Context/Property[@Name='BlockedSPIDCount']$</AlertParameter2>
</AlertParameters>
</AlertSettings>
<OperationalStates>
<OperationalState ID="Success" MonitorTypeStateID="Healthy" HealthState="Success"/>
<OperationalState ID="Error" MonitorTypeStateID="Critical" HealthState="Error"/>
</OperationalStates>
<Configuration>
<ServiceName>$Target/Host/Property[Type='ASCore!Microsoft.SQLServer.AnalysisServices.Core.Instance']/ServiceName$</ServiceName>
<ConnectionString>$Target/Host/Property[Type='ASCore!Microsoft.SQLServer.AnalysisServices.Core.Instance']/ConnectionString$</ConnectionString>
<DatabaseName>$Target/Property[Type='ASCore!Microsoft.SQLServer.AnalysisServices.Core.Database']/DatabaseName$</DatabaseName>
<Threshold>10</Threshold>
<SampleCount>4</SampleCount>
<WaitMinutes>1</WaitMinutes>
<IntervalSeconds>900</IntervalSeconds>
<SyncTime/>
<TimeoutSeconds>300</TimeoutSeconds>
</Configuration>
</UnitMonitor>