Architecture of Query Execution—Retrieving Data from Storage - Microsoft SQL Server 2008 Analysis Services - page 554

type of request is called an OR query ). For example, if a user requests data about
customers that live in Seattle or Redmond, the subcube will contain a set with two
members: Seattle and Redmond . If the partition metadata contains information that
the current partition has data only about customers that live in Redmond and
Bellevue, Analysis Services rewrites the original subcube. The updated subcube will
contain a single slice— Redmond —which Analysis Services can handle much more effi-
ciently than a slice with a set of members.

. Analysis Services detects the decoding tables that it will use during query execution. It

tries to use fewer decoding tables to decode more attributes. Because decoding tables
are built using native hierarchies, Analysis Services tries to find native hierarchies
with larger number of levels that can help to decode larger number of attributes.

If Analysis Services has successfully initialized the Query Partition job, it references it into
a Query Measure Group job. Analysis Services then starts the Query Measure Group job,
and Job Coordinator executes multiple Query Partition jobs in parallel. Analysis Services
uses the CoordinatorExecutionMode server configuration property to define the number of
partitions from one measure group that it can query in parallel. By default, the value of
the CoordinatorExecutionMode is -4, which means 4 multiplied by number of the proces-
sors on the machine. If you change this value to 0, Analysis Services will try to run all the
jobs in parallel. And if you set it to the positive number, Analysis Services will use it as
number of jobs to run in parallel.

Partitions are MapStore objects and divided into segments by 64K records. To read data
from each segment of a partition, Analysis Services creates a Segment job. However, a
partition can contain hundreds of segments, and Analysis Services can't start all this jobs
in parallel. You can use the CoordinatorQueryMaxThreads server configuration property to
configure the number of segments of one partition that Analysis Services queries in paral-
lel. By default, each Analysis Services doesn't start more than 16 Segment jobs per parti-
tion simultaneously, and starts more jobs only when one of the running jobs completes.

A Segment job requires a single thread, which Analysis Services retrieves from the thread
pool (see Chapter 28, “Thread Pool”). The number of threads in a pool is limited, so the
number of started jobs can't exceed the number of available threads in the thread pool.

The large number of jobs executed in parallel can overload the system and lower the
server performance, because jobs will compete for resources. This typically happens with a
machine that has a large number of CPUs, where by default the number of jobs running
in parallel can became really high. If you notice that your system slows down because of a
large number of jobs executing at the same time, we recommend lowering the number of
partitions or segments queried in parallel.

Another negative effect of this architecture can show up in the system with many users.
When a query from one user utilizes all system resources, a query from other users can
wait a long time before it gets resources required for execution. To make the system more
responsive to queries from multiple users, you can use the server configuration property
CoordinatorQueryBalancingFactor . When the CoordinatorQueryBalancingFactor prop-
erty is set to a value larger than 0, Analysis Services limits the number of threads used by a