Thread Management - Microsoft SQL Server 2008 Analysis Services - page 518

not stop accepting user connections while performing a processing operation. However,
this approach increases the complexity of the system and requires caution when changing
the configuration settings of a thread pool, which might require changing the settings of
other thread pools to keep the system in balance. Understanding the inner workings of
thread pools can help you fine-tune and troubleshoot your system's throughput.

Thread Pools

Analysis Services uses four thread pools to support three major operation types:

. Command execution (parser)

. Calculation execution (formula engine)

. Access to data stored on the server (storage engine)

Parsing is supported by two pools: short-running requests (parsed by threads from the
short-command parsing pool) and long-running requests (parsed by the threads from the
long-command parsing pool).

Analysis Services chooses commands that do not require the use of a lot of system
resources being executed by threads from the short-command parsing pool. Threads from
the short-command parsing pool parse the command, immediately execute it, and gener-
ate the response. Discover requests, and metadata operations that include the creation or
deletion of objects, are good examples of such commands. Those commands spend a
larger portion of their execution time on the result serialization because data for such
requests typically is already loaded in memory (which allows for faster execution).
Therefore, threads from the short-command parsing pool are used for a relatively short
period of time and return to the pool in a short cycle. The short-command parsing pool
enables independent and fast retrieval of a server's metadata and system statistics at any
time, even when Analysis Services is under heavy load.

Commands that require a lot of system resources and use other server subsystems (such as
the formula engine and storage engine) are parsed by the threads from the long-
command parsing pool. Threads from the long-command parsing pool parse the
command and immediately turn control over to another thread (from a different thread
pool). However, the parsing thread is not returned to the thread pool until a command
finishes execution and the results are passed back to the client. The turnaround speed of
threads in the long-command parsing pool varies depending on the duration of the
request. In many cases, throughput of Multidimensional Expressions (MDX) queries and
processing commands depends on the number of idle threads available in the long-
command parsing pool. If you decrease the number of threads in this pool, the parsing
stage might become bottlenecked at command execution. However, you might notice that
individual commands are running faster because they have more resources available
during the later stages of execution.

The query-execution pool supplies threads to the formula engine subsystem that performs
calculations of MDX. We chose to separate this pool from the process-execution pool to