SSAS 2012: Collect Database Blocking Duration (minutes)

Microsoft.SQLServer.2012.AnalysisServices.PerformanceRule.MultidimensionalDatabase.BlockingDuration (Rule)

The rule collects the longest blocking duration for sessions that are currently blocked.

Knowledge Base article:

Summary

The rule collects the longest blocking duration for sessions currently blocked. This can be used to allow administrators to readily detect blocking issues and respond accordingly.

External

Disconnect Users and Sessions on Analysis Services Server

TechNet documentation for DISCOVER_SESSSIONS rowset

Element properties:

Target	Microsoft.SQLServer.2012.AnalysisServices.MultidimensionalDatabase
Category	PerformanceCollection
Enabled	True
Alert Generate	False
Remotable	True

Member Modules:

ID	Module Type	TypeId	RunAs
DS	DataSource	Microsoft.SQLServer.2012.AnalysisServices.DataSource.BlockedSessions	Microsoft.SQLServer.2012.AnalysisServices.RunAsProfile.ASMonitoring
Mapper	ConditionDetection	System.Performance.DataGenericMapper	Default
WriteToDatabase	WriteAction	Microsoft.SystemCenter.CollectPerformanceData	Microsoft.SQLServer.2012.AnalysisServices.RunAsProfile.ASMonitoring
WriteToDW	WriteAction	Microsoft.SystemCenter.DataWarehouse.PublishPerformanceData	Microsoft.SQLServer.2012.AnalysisServices.RunAsProfile.ASMonitoring

Source Code:

<Rule ID="Microsoft.SQLServer.2012.AnalysisServices.PerformanceRule.MultidimensionalDatabase.BlockingDuration" Enabled="true" Target="SQLAS!Microsoft.SQLServer.2012.AnalysisServices.MultidimensionalDatabase" ConfirmDelivery="false" Remotable="true" Priority="Normal" DiscardLevel="100">

  <Category>PerformanceCollection</Category>

  <DataSources>

    <DataSource ID="DS" TypeID="Microsoft.SQLServer.2012.AnalysisServices.DataSource.BlockedSessions" RunAs="SQLAS!Microsoft.SQLServer.2012.AnalysisServices.RunAsProfile.ASMonitoring">

      <ServiceName>$Target/Host/Property[Type='SQLAS!Microsoft.SQLServer.2012.AnalysisServices.Instance']/ServiceName$</ServiceName>

      <ConnectionString>$Target/Host/Property[Type='SQLAS!Microsoft.SQLServer.2012.AnalysisServices.Instance']/ConnectionString$</ConnectionString>

      <DatabaseName>$Target/Property[Type="SQLAS!Microsoft.SQLServer.2012.AnalysisServices.Database"]/DatabaseName$</DatabaseName>

      <WaitMinutes>0</WaitMinutes>

      <IntervalSeconds>900</IntervalSeconds>

      <SyncTime/>

    </DataSource>

  </DataSources>

  <ConditionDetection ID="Mapper" TypeID="SystemPerf!System.Performance.DataGenericMapper">

    <ObjectName>Multidimensional Database</ObjectName>

    <CounterName>Blocking Duration (minutes)</CounterName>

    <InstanceName>$Target/Property[Type="SQLAS!Microsoft.SQLServer.2012.AnalysisServices.Database"]/DatabaseName$</InstanceName>

    <Value>$Data/Property[@Name='WaitTime']$</Value>

  </ConditionDetection>

  <WriteActions>

    <WriteAction ID="WriteToDatabase" TypeID="SC!Microsoft.SystemCenter.CollectPerformanceData" RunAs="SQLAS!Microsoft.SQLServer.2012.AnalysisServices.RunAsProfile.ASMonitoring"/>

    <WriteAction ID="WriteToDW" TypeID="SCDW!Microsoft.SystemCenter.DataWarehouse.PublishPerformanceData" RunAs="SQLAS!Microsoft.SQLServer.2012.AnalysisServices.RunAsProfile.ASMonitoring"/>

  </WriteActions>

</Rule>