One of the key performance concepts in Analysis Services is the design and use of good Aggregations to support user queries. However, Analysis Services (Both 2005 and 2008) by default is not configured very well to take advantage of multi-processor environments for parallel processing of these indexes, particularly for large MOLAP partitions. Keep in mind, using a large portion of available processors for aggregation building is only desirable in environments where a processing window is allocated and user activity is minimized or prevented entirely for that window. If you have small datasets and processing time is not a problem then making changes to support better parallel processing may not be needed or recommended. Changing of these settings is best done under guidance of Microsoft.
Problem Isolation: Using a combination of SQL Server Profiler and watching the Task Manager from windows allows simple detection of the key symptom of this problem: seeing only one processor (or processor core) being utilized while processing of aggregations is taking place. To test this, issue a multi-partition ProcessIndexes XMLA statement such as the one below and watch your Profiler and Task Manager (could also use Perfmon as well).
<Batchxmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Parallel>
<Processxmlns:xsd="http://www.w3.org/2001/XMLSchema"xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2"xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2"xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100">
<Object>
<DatabaseID>Adventure Works DW 2008</DatabaseID>
<CubeID>Adventure Works</CubeID>
<MeasureGroupID>Fact Internet Sales 1</MeasureGroupID>
<PartitionID>Internet_Sales_2001</PartitionID>
</Object>
<Type>ProcessIndexes</Type>
<WriteBackTableCreation>UseExisting</WriteBackTableCreation>
</Process>
<Processxmlns:xsd="http://www.w3.org/2001/XMLSchema"xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2"xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2"xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100">
<Object>
<DatabaseID>Adventure Works DW 2008</DatabaseID>
<CubeID>Adventure Works</CubeID>
<MeasureGroupID>Fact Internet Sales 1</MeasureGroupID>
<PartitionID>Internet_Sales_2002</PartitionID>
</Object>
<Type>ProcessIndexes</Type>
<WriteBackTableCreation>UseExisting</WriteBackTableCreation>
</Process>
<Processxmlns:xsd="http://www.w3.org/2001/XMLSchema"xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2"xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2"xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100">
<Object>
<DatabaseID>Adventure Works DW 2008</DatabaseID>
<CubeID>Adventure Works</CubeID>
<MeasureGroupID>Fact Internet Sales 1</MeasureGroupID>
<PartitionID>Internet_Sales_2003</PartitionID>
</Object>
<Type>ProcessIndexes</Type>
<WriteBackTableCreation>UseExisting</WriteBackTableCreation>
</Process>
<Processxmlns:xsd="http://www.w3.org/2001/XMLSchema"xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2"xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2"xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100">
<Object>
<DatabaseID>Adventure Works DW 2008</DatabaseID>
<CubeID>Adventure Works</CubeID>
<MeasureGroupID>Fact Internet Sales 1</MeasureGroupID>
<PartitionID>Internet_Sales_2004</PartitionID>
</Object>
<Type>ProcessIndexes</Type>
<WriteBackTableCreation>UseExisting</WriteBackTableCreation>
</Process>
</Parallel>
</Batch>If, during the course of processing you see only one partition processing at a time (in the Profiler window) and that also correlates to mdsmdsrv.exe only using the equivalent of one CPU (i.e. ~50% on a 2-CPU machine) then tuning the SSAS server properties to allow better parallel aggegation/index processing might be an advantage for the environment (see original statement regarding processing windows and data sets in the first paragraph).
Configuration Changes: The configuration properties of AggregationMemoryLimitMax and AggregationMemoryLimitMin (these are in the OLAP \ Process section of the Analysis Services Properties Window):
The default value of 80 for AggregationMemoryLimitMax will prevent more than one partition from being processed, especially for large cubes/partitions with large aggregations. This is because a setting of 80 means up to 80% of the available memory can be used for building aggregations on the partition. That leaves only 20% of the available memory for other partitions, which is not enough to start another partition until the partition currently being processed is completed. I use the following formula for setting AggregationMemoryLimitMax and AggregationMemoryLimitMin (this is only used for reference, the proper settings vary from environment to environment based on hardware resources, business requirements and availability requirements):
AggregationMemoryLimitMax=ceiling(100/(<number processing cores>-2))
AggregationMemoryLimitMax=ceiling(100/((<number processing cores>-2)*1.5))
Using (<number processing cores>+1) helps to leave enough memory for at least one processor core for non-SSAS activities (such as paging, SQL Server activities).
On a 16-core machine the AggregationMemoryLimitMax would come out to 7 which means up to 14 (100/7) partitions could process aggregations in parallel. The AggregationMemoryLimitMin would come to 5 which means no more than 20 (100/5) partitions could process in parallel.
Result:
Processing time for rebuilding aggregations and indexes is significantly reduced in multi-processor environments, provided the environment does not have other resource constraints. The degree of improvement will depend on the number of processing cores and amount of memory available to the system for the Aggregation processing. However, on large mult-core, multi-processor servers with large partitions and aggregations, this can make the difference between being able to process the aggregations frequent enough to help user queries or having the user queries suffer longer durations due to missing indexes and/or aggregations that are not processed.
Print | posted on Tuesday, November 02, 2010 12:33 AM | Filed Under [ Analysis Services ]
