SQL Analysis Threads Test

SSAS has been designed to perform its operations in parallel and because of this it can create multiple threads to execute multiple requests in parallel. Since creating and destroying threads is an expensive affair, SSAS maintains two sets of worker thread pools to return threads which are currently not being used, so that SSAS can again pick them up for serving other requests. These two pools are called the Query Thread Pool and the Process Thread Pool.

The XMLA listener listens for incoming requests and creates (or pulls out a query thread from the query pool if one is already available) a query thread which checks for data/calculations in the formula engine cache. If required, the XMLA listener creates (or pulls out a process thread from the process pool if one is already available) a process thread which is used to retrieve data from the storage engine cache/disk. The process thread also stores the data in the storage engine cache which it retrieved from the disk whereas the query thread stores the calculations in the formula engine cache to resolve/serve future queries.

The more threads SSAS can create, the more requests can be served in parallel. If sufficient threads are not available in the pools, then processing bottlenecks in SSAS become inevitable! To prevent processing delays therefore, administrators must first understand how the threads in the processing and query thread pools are utilized, proactively detect potential contention for threads, and reconfigure the SSAS to spawn more threads. This is where the SQL Analysis Threads test helps!

This test monitors the I/O and non-I/O thread usage in the query and processing thread pools, and alerts administrators to excessive/abnormal thread utilization in any of the pools. Additionally, the test also tracks the jobs in the queue of every pool, thus revealing if the pools are sized commensurate to the workload. Administrators are notified if the job queue length keeps increasing, thereby revealing a dearth of idle threads in the pool.

Target of the test : A Microsoft SQL Analysis Services Server

Agent deploying the test : An internal agent

Outputs of the test : One set of results for the SSAS server being monitored

Configurable parameters for the test
Parameters	Description
Test Period	How often should the test be executed.
Host	The host for which the test is to be configured.
Port	The port at which the specified Host listens.
Instance	The name of a specific SQL Analysis Services Server instance to be monitored. The default value of this parameter is “default”. However, if the Microsoft SQL Server hosting SSAS uses named instances, then do the following: Configure the Instance parameter with the name of the SQL Server instance that hosts SSAS. Do not change the default value of the Port parameter
SSAS Version	Specify the SSAS version being monitored. By default, 11 is displayed here, indicating that version 11 of SSAS is monitored by default.

Measurements made by the test
Measurement	Description	Unit	Interpretation
Threads running non-I/O jobs	Indicates the number of non-I/O threads in the processing pool that are currently busy.	Number	Non-I/O threads are those that run non-I/O jobs. A high value for this measure is a sign that threads in the processing pool are engaged in running many non-I/O jobs.
Idle threads dedicated to non-I/O jobs	Indicates the number of non-I/O threads in the processing pool that are currently .	Number	Non-I/O threads are those that run non-I/O jobs. Ideally, the value of this measure should be high, as the presence of idle threads in a pool is a sign that threads are available for processing future queries.
Non-I/O jobs in the queue	Indicates the number of non-I/O jobs in the queue of the processing thread pool.	Number	A consistent increase in the value of this measure indicates that SSAS is unable to process non-I/O jobs as quickly as the job requests come in. This can happen if there are not enough threads in the processing thread pool to run the jobs. You may want to allocate more threads to the pool, so that jobs are executed without any delay. For that, you can use the ThreadPool\Process\MaxThreads and ThreadPool\Process\MinThreads properties of the SSAS instance. By default, the maximum number of process threads that be created is either 64 or 10*number of cores on the machine (whichever is higher). Where job load is high, you may want to increase MaxThreads, so that more jobs can be run in parallel, ultimately reducing the job queue length. You can also reduce the job queue length, by changing the value of PerNumaNode property to 0. This will disable the per NUMA node thread pool behavior. You can also consider adding more CPU cores and/or adjusting the CoordinatorExecutionMode property.
Non-I/O jobs rate	Indicates the rate at which the processing thread pool processed non-I/O jobs.	Jobs/Sec	A high value is desired for this measure. A steady drop in this value could indicate a processing bottleneck.
Threads running I/O jobs	Indicates the number of I/O threads in the processing pool that are currently busy.	Number	I/O threads are those that run non-I/O jobs. A high value for this measure is a sign that threads in the processing pool are engaged in running many I/O jobs.
Idle threads dedicated to I/O jobs	Indicates the number of I/O threads in the processing pool that are currently idle.	Number	I/O threads are those that run non-I/O jobs. Ideally, the value of this measure should be high, as the presence of idle threads in a pool is a sign that threads are available for processing future queries.
I/O jobs in queue	Indicates the number of I/O jobs in the queue of the processing thread pool.	Number	A consistent increase in the value of this measure indicates that SSAS is unable to process I/O jobs as quickly as the job requests come in. This can happen if there are not enough threads in the processing thread pool to run the jobs. You may want to allocate more threads to the pool, so that jobs are executed without any delay. For that, you can use the ThreadPool\Process\MaxThreads and ThreadPool\Process\MinThreads properties of the SSAS instance. By default, the maximum number of process threads that be created is either 64 or 10*number of cores on the machine (whichever is higher). Where job load is high, you may want to increase MaxThreads, so that more jobs can be run in parallel, ultimately reducing the job queue length. You can also reduce the job queue length, by changing the value of PerNumaNode property to 0. This will disable the per NUMA node thread pool behavior. You can also consider adding more CPU cores and/or adjusting the CoordinatorExecutionMode property.
I/O jobs rate	Indicates the rate at which the processing thread pool processed I/O jobs.	Jobs/Sec	A high value is desired for this measure. A steady drop in this value could indicate a processing bottleneck.
Busy threads	Indicates the number of threads in the query thread pool that are currently busy.	Number	A high value for this measure is a sign that threads in the processing pool are engaged in servicing queries.
Idle threads	Indicates the number of threads in the query thread pool that are currently idle.	Number	Ideally, the value of this measure should be high, as the presence of idle threads in a pool is a sign that threads are available for processing future queries.
Jobs in the queue	Indicates the number of jobs in the queue of the query thread pool.	Number	A consistent increase in the value of this measure indicates that SSAS is unable to service queries as quickly as they come in. This can happen if there are not enough threads in the query thread pool to run the queries. You may want to allocate more threads to the pool, so that queries are executed without any delay. For that, you can use the ThreadPool\Query\MinThreads and ThreadPool\Query\MaxThreads properties of the SSAS instance. By default, the maximum number of query threads that can be created is either10 or 2*number of cores on the machine (whichever is higher). Where query load is high, you may want to increase MaxThreads, so that more queries can be run in parallel, ultimately reducing the job queue length.
Query pool job rate	Indicates the rate at which the query thread pool processed query jobs.	Jobs/Sec	A high value is desired for this measure. A steady drop in this value could indicate a query processing bottleneck.