Databases Reference
In-Depth Information
Parallelism during Processing
When compared to Analysis Services 2000, Analysis Services 2005 has improved
processing behavior by using max parallelism to process independent components
by default. If you have 16 partitions, all 16 partitions will be processed in parallel.
Having too much parallelism can actually hurt performance through context switch-
ing and disk thrashing — happily, Analysis Services provides you with several op-
tions to control the amount of parallelism used for processing. Based on the com-
plexity of the cube and the set of aggregations, we suggest you have two to three
objects being processed per CPU.
You can control the amount of parallelism by changing certain server properties.
For processing, the main server property impacting processing performance is
CoordinatorExecutionMode. The server properties can be changed using the prop-
erties dialog from SQL Server Management Studio or in the config file
msmsdsrv.ini located in the %System Drive%\Program Files\Microsoft SQL Server\
MSSQL.x\OLAP\Config folder. The CoordinatorExecutionMode server property
sets the maximum parallelism allowed for a specific job, such as processing that
needs to be executed on the server at a specific time. This property can have a
positive or negative value. If the value is positive, the actual value is used and if
the value is negative, it is multiplied by the number of processors and the absolute
value of the result is used. For example, the default Analysis Services 2005 value
is -4, which on a 4-processor machine indicates that the maximum parallelism to
be used on the machine is 16 for each request. By setting this property you can
avoid the server being overloaded with processing operations, perhaps to allow
some resources for queries.
Another property, ThreadPool\Processing\MaxThreads, specifies the maximum
number of threads in the processing thread pool. We recommend this not be used
as a way to limit concurrency because internally the server often executes tasks by
queuing other tasks and waiting for completion. The server is designed to be smart
enough to know that more threads are needed to avoid deadlocking by exceeding
MaxThreads.
When all the objects within a database are processed in parallel then
Analysis Services sends queries to the data source for dimension as
well as partition processing. Sometimes having too many connections
and queries to the data source can increase processing time. You can
limit the number of connections Analysis Services establishes with the
Search WWH ::
Custom Search