The monitor alerts when the number of sessions that are blocked longer than the configured WaitMinutes setting exceeds the configured threshold.
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 an administrator may use this monitor to be aware when this condition occurs for longer than the configured WaitMinutes setting for a number of sessions greater than the configured threshold.
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.
Resolution of blocking issues on a server requires knowledge of workload, requirements, and users, which an administrator must consider when deciding on a correct course of action for remediation. Remediation of blocking issues might include many potential actions, including but not limited to the following:
Adjust SSAS configuration settings to increase parallelism to avoid blocking due to resource contention. Configuration changes of this nature should not be performed on production databases with moderate or high business impact without first testing and fully understanding implications. An administrator should thoroughly review the SSAS Multidimensional Performance Guide (Chapter 3 in general, and specifically section 3.8 regarding multi-user performance considerations) prior to any such change made without the direct consultation and direction of Microsoft support, since otherwise server performance may be affected adversely rather than improved.
Improve query performance in general for queries identified to be blocking.
Identify blocking session including processing operation, reduce the blocking by proper scheduling of processing jobs, tuning queries and setting proper timeout configuration.
Review database design for efficiency. Long running blocking jobs on a server may be improved through design or configuration changes to reduce their degree of locking, scheduled for a time when blocking will not occur as severely, or additional resources can be added to the server as determined necessary through a review of performance counters to determine wherein performance bottleneck(s) occur, which lead to the long blocking scenarios.
Increase configured threshold and/or configured WaitMinutes setting to avoid the alert.
Disable the monitor entirely if blocking is not a concern on the server.
Configure Server Properties in Analysis Services
TechNet documentation for SSAS Performance Counters
SSAS Multidimensional Performance Guide
Name | Description | Default Value |
Enabled | Enables or disables the workflow | True |
Generates Alerts | Defines whether the workflow generates an Alert | True |
Interval Seconds | The recurring interval of time in seconds in which to run the workflow. | 900 |
Number of samples | Health State changes if the number of threshold breaches is greater than or equal to the Minimum Number of Breaches. | 3 |
Synchronization Time | The synchronization time specified by using a 24-hour format. May be omitted. |
|
Critical Threshold | Health State changes when then number of blocked sessions exceeds the threshold. | 10 |
Wait Minutes | Wait Minutes parameter defines the minimum waiting time for the session to be considered by monitor. | 1 |
Target | Microsoft.SQLServer.2014.AnalysisServices.TabularDatabase | ||
Parent Monitor | System.Health.PerformanceState | ||
Category | PerformanceHealth | ||
Enabled | True | ||
Alert Generate | True | ||
Alert Severity | Error | ||
Alert Priority | Normal | ||
Alert Auto Resolve | True | ||
Monitor Type | Microsoft.SQLServer.2014.AnalysisServices.MonitorType.Database.BlockedSessionsNumber | ||
Remotable | True | ||
Accessibility | Public | ||
Alert Message |
| ||
RunAs | Default |
<UnitMonitor ID="Microsoft.SQLServer.2014.AnalysisServices.UnitMonitor.TabularDatabase.BlockedSessionsNumber" Accessibility="Public" Enabled="true" Remotable="true" Priority="Normal" Target="SQLAS!Microsoft.SQLServer.2014.AnalysisServices.TabularDatabase" TypeID="Microsoft.SQLServer.2014.AnalysisServices.MonitorType.Database.BlockedSessionsNumber" ParentMonitorID="SystemHealth!System.Health.PerformanceState" ConfirmDelivery="false">
<Category>PerformanceHealth</Category>
<AlertSettings AlertMessage="Microsoft.SQLServer.2014.AnalysisServices.UnitMonitor.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='SQLAS!Microsoft.SQLServer.2014.AnalysisServices.Instance']/ServiceName$</ServiceName>
<ConnectionString>$Target/Host/Property[Type='SQLAS!Microsoft.SQLServer.2014.AnalysisServices.Instance']/ConnectionString$</ConnectionString>
<DatabaseName>$Target/Property[Type="SQLAS!Microsoft.SQLServer.2014.AnalysisServices.Database"]/DatabaseName$</DatabaseName>
<Threshold>10</Threshold>
<SampleCount>4</SampleCount>
<WaitMinutes>1</WaitMinutes>
<IntervalSeconds>900</IntervalSeconds>
<SyncTime/>
</Configuration>
</UnitMonitor>