• Management Pack:  SQL Server Analysis Services
  • MP Version:  6.7.31.0 for SQL Server 2016 Analysis Services
  • Released:  6/27/2017
  • Publisher:  Microsoft

Blocking Duration Monitor

  • ID:  Microsoft.SQLServer.2016.AnalysisServices.UnitMonitor.MultidimensionalDatabase.BlockingDuration
  • Description:  The monitor alerts, if at least one session is blocked for a longer period than the configured threshold.
  • Target:  SSAS 2016 Multidimensional DB
  • Enabled:  Yes

Operational States

Name State Description
Blocking Duration Acceptable Success  
Blocking Duration Warning Warning  

Overridable Parameters

Parameter Name Default Value Description Override
Interval Seconds 900 The recurring interval of time in seconds in which to run the workflow.
Synchronization Time   The synchronization time specified by using a 24-hour format. May be omitted.
Warning Threshold (min) 1 Health State changes, if at least one session is blocked longer than the threshold.
Timeout (seconds) 300 Specifies the time the workflow is allowed to run before being closed and marked as failed.

Alert Details

Monitor State Message Priority Severity Auto Resolution
Blocking Duration Warning (Warning) SSAS 2016: At least one session has been blocked for a long period. Medium Warning Yes

Run As Profiles

Name
Default

Monitor Knowledgebase

Summary

The monitor alerts, when at least one 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.

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:

  • 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 thoroughly review the SSAS Multidimensional Performance Guide 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.

  • Identify blocking sessions and manually terminate them. It should be noted that a known issue with the product can block the cancellation of long running queries.

  • Review database design for efficiency, again, based upon the valuable best practices suggested in the SSAS Multidimensional Performance Guide . 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 the configured threshold to avoid the alert.

  • Disable the monitor entirely, if blocking is not a concern on the server.

External References

Configure Server Properties in Analysis Services

TechNet documentation for SSAS Performance Counters

SSAS Multidimensional Performance Guide

FIX: It takes longer than expected to cancel an MDX query that contains many GROUP BY clauses in SSAS 2016

See Also for SQL Server Analysis Services Management Pack


Downloads for SQL Server Analysis Services Management Pack

AZURE OPTIMIZATION ASSESSMENT GET STARTED
MIGRATION TO AZURE GET STARTED
SYSTEM CENTER MIGRATION TO AZURE GET STARTED
MIGRATION TO AZURE FOR SQL AND WINDOWS 2008 GET STARTED