Home
  •  

Query Pool Queue length

Microsoft.SQLServer.2008.AnalysisServices.UnitMonitor.Instance.QueryPoolQueueLength (UnitMonitor)

The monitor alerts when the size of query pool queue for the SSAS instance is greater than the configured threshold.

Knowledge Base article:

Summary

The monitor alerts when the size of the query pool job queue exceeds the configured threshold. Queuing can indicate that some requests may not be executed due to waiting for a free thread from the pool, even though the jobs might of themselves execute quickly and return.

Causes

Exhaustion of the available threads from the query pool can occur for a number of reasons including misconfiguration of the server’s settings affecting the pool or abnormally high demand by users or scheduled jobs on the instance.

Resolutions

Resolving this alert requires administrators to understand and consider numerous factors including but not limited to workload requirements, user and job schedules, other scheduled processes on the server computer, and resources available. Actions to remediate the condition when the length of the query pool job queue exceeds the configured threshold may include any of the following actions:

External

Configure Server Properties in Analysis Services

SSAS Multidimensional Performance Guide

Element properties:

TargetMicrosoft.SQLServer.2008.AnalysisServices.Instance
Parent MonitorSystem.Health.PerformanceState
CategoryPerformanceHealth
EnabledTrue
Alert GenerateTrue
Alert SeverityWarning
Alert PriorityNormal
Alert Auto ResolveTrue
Monitor TypeMicrosoft.SQLServer.2008.AnalysisServices.MonitorType.PerformanceCounterThreads
RemotableTrue
AccessibilityPublic
Alert Message
SSAS 2008: Query Pool Job Queue length exceeded
The query pool job queue length for the SSAS instance {1} on computer {0} exceeded the configured threshold.

Current Query Queue Length: {2}
RunAsDefault

Source Code:

<UnitMonitor ID="Microsoft.SQLServer.2008.AnalysisServices.UnitMonitor.Instance.QueryPoolQueueLength" Accessibility="Public" Enabled="true" Remotable="true" Priority="Normal" Target="SQLAS!Microsoft.SQLServer.2008.AnalysisServices.Instance" TypeID="Microsoft.SQLServer.2008.AnalysisServices.MonitorType.PerformanceCounterThreads" ParentMonitorID="SystemHealth!System.Health.PerformanceState" ConfirmDelivery="false">

  <Category>PerformanceHealth</Category>

  <AlertSettings AlertMessage="Microsoft.SQLServer.2008.AnalysisServices.UnitMonitor.Instance.QueryPoolQueueLength.AlertMessage">

    <AlertOnState>Warning</AlertOnState>

    <AutoResolve>true</AutoResolve>

    <AlertPriority>Normal</AlertPriority>

    <AlertSeverity>Warning</AlertSeverity>

    <AlertParameters>

      <AlertParameter1>$Target/Host/Property[Type="Windows!Microsoft.Windows.Computer"]/NetworkName$</AlertParameter1>

      <AlertParameter2>$Target/Property[Type="SQLAS!Microsoft.SQLServer.2008.AnalysisServices.ServerRole"]/InstanceName$</AlertParameter2>

      <AlertParameter3>$Data/Context/Value$</AlertParameter3>

    </AlertParameters>

  </AlertSettings>

  <OperationalStates>

    <OperationalState ID="Success" MonitorTypeStateID="Healthy" HealthState="Success"/>

    <OperationalState ID="Warning" MonitorTypeStateID="Warning" HealthState="Warning"/>

  </OperationalStates>

  <Configuration>

    <PerformanceCounterName>Query pool job queue length</PerformanceCounterName>

    <CounterName>Query pool job queue length</CounterName>

    <Threshold>0</Threshold>

    <SampleCount>4</SampleCount>

    <IntervalSeconds>900</IntervalSeconds>

    <SyncTime/>

  </Configuration>

</UnitMonitor>