The monitor alerts, when the size of query pool queue for 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 be executed and returned quickly.
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 the 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 reviewing SSAS Multidimensional Performance Guide, to increase available threads in the query pool. This is not advised without careful consideration and testing, or consultation with Microsoft support.
Decrease workload on 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.
Tune 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.
Configure Server Properties in Analysis Services
SSAS Multidimensional Performance Guide
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 |
Number of samples | Health State changes, if the number of threshold breaches is greater than or equal to the Minimum Number of Breaches. | 4 |
Synchronization Time | The synchronization time specified by using a 24-hour format. May be omitted. |
|
Warning Threshold | Health State changes, if Analysis Services performance counter exceeds the threshold. | 0 |
Timeout (seconds) | Specifies the time the workflow is allowed to run before being closed and marked as failed. | 300 |
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/>
<TimeoutSeconds>300</TimeoutSeconds>
</Configuration>
</UnitMonitor>