The monitor alerts when the size of query pool queue for the SSAS instance is greater than the configured threshold.
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.
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.
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:
Modify pool configuration settings, after consulting the SSAS Multidimensional Performance Guide, to increase available threads in the query pool. This is not advised without very careful consideration and testing, or consultation of Microsoft support.
Decrease workload on the SSAS instance by rescheduling jobs, modifying the design of the database, constraining user queries, or dividing workload to move some work to other servers.
Increase the number of available threads in the pool by increasing the number of available cores on the server, which dynamically raises the pool size.
Increase the configured thresholds for the alert to avoid the condition.
Disable the monitor entirely if query pool job queuing is not a concern on the server.
Target | Microsoft.SQLServer.2008.AnalysisServices.Instance | ||
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.2008.AnalysisServices.MonitorType.PerformanceCounterThreads | ||
Remotable | True | ||
Accessibility | Public | ||
Alert Message |
| ||
RunAs | Default |
<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>