The monitor alerts if at least one session is blocked for a period longer than the configured threshold.
The monitor alerts when at least one SSAS tabular session is blocked for a longer period than 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 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 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:
Adjust SSAS configuration settings to alter priorities for running queries. Configuration changes of this nature should not be performed on production databases with moderate or high business impact without preliminary testing and complete understanding of the implications. The administrator should consult Microsoft support prior to making changes to these settings; otherwise, server performance may be affected adversely.
Identify the blocking sessions and terminate them manually.
Review the database design for efficiency. Long running blocking jobs on a server may be improved through design or configuration changes. Therefore, to reduce their blocking severity the jobs can be scheduled for a time, when the blocking will not have such severe consequences. Additional resources can also be added to the server if performance counters review denotes presence of performance bottleneck(s) leading to long blocking scenarios.
Increase the configured threshold 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
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 |
Synchronization Time | The synchronization time specified by using a 24-hour format. May be omitted. |
|
Warning Threshold (min) | Health state changes if at least one session is blocked longer than the threshold. | 1 |
Timeout (seconds) | Specifies the time the workflow is allowed to run before being closed and marked as failed. | 300 |
Target | Microsoft.SQLServer.AnalysisServices.Windows.TabularDatabase | ||
Parent Monitor | System.Health.PerformanceState | ||
Category | PerformanceHealth | ||
Enabled | True | ||
Alert Generate | True | ||
Alert Severity | Warning | ||
Alert Priority | Normal | ||
Alert Auto Resolve | True | ||
Monitor Type | Microsoft.SQLServer.AnalysisServices.Windows.Monitoring.MonitorType.Database.BlockingDuration | ||
Remotable | True | ||
Accessibility | Public | ||
Alert Message |
| ||
RunAs | Default |
<UnitMonitor ID="Microsoft.SQLServer.AnalysisServices.Windows.Monitoring.Monitor.TabularDatabase.BlockingDuration" Target="SQLAS!Microsoft.SQLServer.AnalysisServices.Windows.TabularDatabase" TypeID="Microsoft.SQLServer.AnalysisServices.Windows.Monitoring.MonitorType.Database.BlockingDuration" 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.BlockingDuration.AlertMessage">
<AlertOnState>Warning</AlertOnState>
<AutoResolve>true</AutoResolve>
<AlertPriority>Normal</AlertPriority>
<AlertSeverity>Warning</AlertSeverity>
<AlertParameters>
<AlertParameter1>$Data/Context/Property[@Name='WaitTime']$</AlertParameter1>
</AlertParameters>
</AlertSettings>
<OperationalStates>
<OperationalState ID="Success" MonitorTypeStateID="Healthy" HealthState="Success"/>
<OperationalState ID="Warning" MonitorTypeStateID="Warning" HealthState="Warning"/>
</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>1</Threshold>
<IntervalSeconds>900</IntervalSeconds>
<SyncTime/>
<TimeoutSeconds>300</TimeoutSeconds>
</Configuration>
</UnitMonitor>