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
Size | vCPU | Memory: GiB | Temp storage (SSD) GiB | Max temp storage throughput: IOPS / Read MBps / Write MBps | Max data disks / throughput: IOPS | Max NICs / Expected network bandwidth (Mbps) |
---|---|---|---|---|---|---|
Standard_D11_v2 | 2 | 14 | 100 | 6000 / 93 / 46 | 8 / 8×500 | 2 / 1500 |
Standard_D12_v2 | 4 | 28 | 200 | 12000 / 187 / 93 | 16 / 16×500 | 4 / 3000 |
Standard_D13_v2 | 8 | 56 | 400 | 24000 / 375 / 187 | 32 / 32×500 | 8 / 6000 |
Standard_D14_v2 | 16 | 112 | 800 | 48000 / 750 / 375 | 64 / 64×500 | 8 / 12000 |
Standard_D15_v2 1 | 20 | 140 | 1,000 | 60000 / 937 / 468 | 64 / 64×500 | 8 / 25000 2 |
1 Instance is isolated to hardware dedicated to a single customer.
2 25000 Mbps with Accelerated Networking.
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
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.