SQL Server Analysis Services Performance Tuning on Azure Virtual Machines

In this post, we will be covering SQL Server Analysis Services (SSAS) Performance tuning best practices on Azure with a bit of detail. Do note that we are referring to running SSAS on virtual machines, rather than as a service. Since Microsoft currently offers both PAAS and IAAS, the deployment options are quite flexible.

We already have an article which covers all the best practices for SQL Server 2016 (& 2017) performance tuning. When you are implementing SQL Server on Azure virtual machines, you would still need to follow the best practices that you typically follow for on-premises.

SQL Server Analysis Services Performance

Before you want to get started with installing SSAS, you will have to choose the best VM that can support your performance requirements. Typically, we recommend a DSv2 memory optimized virtual machines for running these kinds of workloads.

Dv2-series

ACU: 210 – 250

¹ Instance is isolated to hardware dedicated to a single customer.

² 25000 Mbps with Accelerated Networking.

Source

For low latency, high-throughput (IOPS) we recommend going with the Premium Managed disks. This is critical for running SSAS on Azure VM’s, because of its IO access patterns, which is highly demanding in nature. A P30 (1 TB) or P40 (2 TB) disk size should suffice the need for most medium sized SSAS cubes. In case if you want more storage space, go with the unmanaged P4 or the P6 options.

Locally redundant storage is recommended unless it is a business requirement to have multi-region DR planning in place.

Note: The Temp disk on a DSv2 series uses SSD disks. Use it only for caching and not for data.

We recommend “Disk Striping”  to achieve more throughput. It is easy to add additional data-disks and use “Disk Striping.” However, to determine the number of data-disks, you need by analyzing the number of IOPS and bandwidth required for your SSAS Cube.

We recommend setting the stripe size at 256 KB (262144 bytes) for data-warehousing workloads to avoid performance impact due to partition misalignment. You can set this with PowerShell.

For Caching Policy, set it to “Read Caching” only if you are using premium storage disks.

To capture SSAS performance on your Azure VM, use the following perfmon counters to analyze and detect performance issues if any.

Analysis Services MOLAP Performance Guide for SQL Server 2012, 2014, 2016, & 2017 Tuning Processing Performance:

Baseline Performance Monitor Trace (Perfmon.exe)

LogicalDisk(*)Avg. Disk Bytes/Read

LogicalDisk(*)Avg. Disk Bytes/Transfer

LogicalDisk(*)Avg. Disk Bytes/Write

LogicalDisk(*)Avg. Disk sec/Read

LogicalDisk(*)Avg. Disk sec/Transfer

LogicalDisk(*)Avg. Disk sec/Write

MemoryAvailable MBytes

MSOLAP$MD16: Memory*

MSOLAP$MD16: Proc Aggregations*

MSOLAP$MD16: Proc Indexes*

MSOLAP$MD16: Processing*

MSOLAP$MD16: Threads*

Processor(*)% Interrupt Time

Processor(*)% Privileged Time

Processor(*)% Processor Time

Partition Processing Commands

• ProcessData
• ProcessIndexes
• ProcessClearIndexes

ProcessData: Works better results with high parallelism

ProcessIndexes: Works better results with low parallelism

Source: Microsoft

Tuning the Process Index Phase

To monitor the process Index phase, use the following counters:

• MSOLAP: Proc AggregationsRow created/Sec  -> higher values mean better performance and reduced time for ProcessIndexes
• MSOLAP:Proc AggregationsTemp file bytes written/sec -> should be 0

Once we have the correct baseline performance counters, we can adjust the following SSAS properties in msmdsrv.ini to speed up the processing.

Increase the available memory for the ProcessIndexes phase in msmdsrv.ini:

• ProcessAggregationMemoryLimitMax
• ProcessAggregationMemoryLimitMin

You can review AggregationMemoryMax/Min in the “Microsoft SQL server analysis services multidimensional performance and operations guide.”

Under the server properties of SQL Server Management Studio and in msmdsrv.ini, you will find the

following settings:

• OLAPProcessAggregationMemoryLimitMin
• OLAPProcessAggregationMemoryLimitMax

These two settings determine how much memory is allocated for the creation of aggregations and indexes in each partition. When Analysis Services starts partition processing, parallelism is throttled based on the AggregationMemoryMin/Max setting. The setting is per partition. For example, if you start five concurrent partition processing jobs with AggregationMemoryMin = 10, an estimated 50 Percent (5 x 10%) of reserved memory is allocated for processing. If memory runs out, new partition processing jobs are blocked while they wait for memory to become available. On a large memory system, allocating 10 percent of available memory per partition may be too much. Also, Analysis Services may sometimes misestimate the maximum memory required for the creation of aggregates and indexes. If you process many partitions in parallel on a large memory system, lowering the value of AggregationMemoryLimitMin and AggregationMemoryMax may increase processing speed. This works because you can drive a higher degree of parallelism during the process index phase.

Like the other Analysis Services memory settings, if this setting has a value higher than 100, it is interpreted as a fixed amount of kilobytes, and if is lower than 100, it is understood as a percentage of the memory is available to Analysis Services. For machines with large amounts of memory and many partitions, using an absolute kilobyte value for these settings may provide better control of memory than using a percentage value. Also, review this blog post that provides some empiric formula to estimate good values for AggregationMemoryMax

Source

AggregationMemoryLimitMax=ceiling(100/(<number processing cores>-2))

AggregationMemoryLimitMin=ceiling(100/((<number processing cores>-2)*1.5))

Source & Download Link: “Microsoft SQL server analysis services multidimensional performance and operations guide.”

Keep in mind that SSAS 2012 or greater use different defaults for the ThreadPool configuration. It is necessary to check the msmdsrv.log file to identify the correct setting.

Below the SSAS’s properties that could affect the performances.

ThreadPoolQuery

<MaxThreads>100</MaxThreads>

<MinThreads>1</MinThreads>

ThreadPoolProcess

<MaxThreads>180</MaxThreads>

<MinThreads>1</MinThreads>

Memory

<MemoryHeapType>2</MemoryHeapType>

<HeapTypeForObjects>0</HeapTypeForObjects>

<HardMemoryLimit>0</HardMemoryLimit>

<TotalMemoryLimit>80</TotalMemoryLimit>

<CoordinatorExecutionMode>-4</CoordinatorExecutionMode>

<CoordinatorShutdownMode>0</CoordinatorShutdownMode>

<CoordinatorQueryBalancingFactor>1</CoordinatorQueryBalancingFactor>

<CoordinatorQueryBoostPriorityLevel>3</CoordinatorQueryBoostPriorityLevel>

Process

<AggregationMemoryLimitMin>1</AggregationMemoryLimitMin>

<AggregationMemoryLimitMax>2</AggregationMemoryLimitMax>

So my suggestion is to adjust the AggregationMemoryLimitMax/Min according to with your VMs Available Memory and number of cores.

Outcomes and Actionable:

• Collect baseline perfmon and profiler trace during the Process Data / Process Indexes phase.
• Change the msmdsrv.ini according to suggested values mentioned above.
• Collect new perfmon / profiler trace with Data collector during the Process Data / Process Indexes phase.
• Check results and compare results.

Disclaimer: The Questions and Answers provided on https://gigxp.com are for general information purposes only. We make no representations or warranties of any kind, express or implied, about the completeness, accuracy, reliability, suitability or availability with respect to the website or the information, products, services, or related graphics contained on the website for any purpose.