SSAS Hardware Configuration Recommendations

By: Dinesh Asanka | Comments (1) | Related: > Analysis Services Administration

Problem

SQL Server Analysis Service (SSAS) is a different service for Microsoft SQL Server compared to the database engine service. Therefore, hardware configurations for SSAS need to be addressed differently. What are the best practices?

Solution

Memory, processor and storage are the major components which need to be looked at and configured to optimize SQL Server, including SSAS. We will take a look at each of these.

Operating System

Although SQL Server runs on Linux, SSAS only runs on Windows operating systems. Only the database engine and SSIS are supported on Linux. Also, SQL Server Agent on Linux does not support SQL Server Analysis Command and SQL Server Analysis Query.

Memory

SQL Server Analysis Service is a memory intensive service. Even if SSAS is idle, it will consume around 40-50 MB of memory. When the load on SSAS increases memory, usage increases rapidly. This threshold limit of memory can be controlled from for the SSAS services. The following settings can be obtained from the General tab of the SSAS properties.

The same settings can be updated from an XMLA script as shown below.

<Alter AllowCreate="true" ObjectExpansion="ObjectProperties" xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
  <Object />
  <ObjectDefinition>
    <Server>
      <ID>DA</ID>
      <Name>DA</Name>
      <ServerProperties>
        <ServerProperty>
          <Name>Memory\VertiPaqMemoryLimit</Name>
          <Value>100</Value>
        </ServerProperty>
        <ServerProperty>
          <Name>Memory\TotalMemoryLimit</Name>
          <Value>90</Value>
        </ServerProperty>
        <ServerProperty>
          <Name>Memory\LowMemoryLimit</Name>
          <Value>70</Value>
        </ServerProperty>
        <ServerProperty>
          <Name>Memory\HardMemoryLimit</Name>
          <Value>12</Value>
        </ServerProperty>
      </ServerProperties>
    </Server>
  </ObjectDefinition>
</Alter>

The following table explains the details about these settings. Since SSAS has two versions, Multi-Dimensional instances and Tabular instances, some settings apply to only one version and some to both.

Memory Setting	Applicable	Description
HardMemoryLimit	Tabular and Multi-Dimensional	This defines a level at which the SSAS instance starts to reject requests completely due to memory usage. The default value for this setting is 0, which automatically places the threshold midway between the TotalMemoryLimit value and either the physical memory or virtual address space, whichever is smaller.
LowMemoryLimit	Multi-Dimensional	This is the lowest limit at which the SSAS instance will begin to release memory allocated to infrequently used objects.
TotalMemoryLimit	Tabular and Multi-Dimensional	This is an upper threshold at which the SSAS instance begins releasing memory more aggressively to make room for currently executing requests and new high-priority requests. This limit applies to both multidimensional and tabular instances. The default value is 80 percent of either physical memory or virtual address space, whatever the smaller.
VertiPaqMemoryLimit	Tabular	This is the lowest threshold at which the SSAS instance begins releasing memory allocated to infrequently used objects. If memory paging to disk is enabled, this value also defines the level of memory consumption at which memory paging should start. The default value is 60.

The user can set a percentage value or a specific value. If the value is less than 100, it will consider it a percentage if not it will be the value entered. Percentages are used as a better option.

In addition to the above settings, you have the option of setting the VertiPaqPagingPolicy property to 0 to disable memory paging to disk for a tabular instance. This will generate an out-of-memory error if no more memory is available. Setting the value to 1 enables paging to disk. Ideally, this should be 0 for better performance, but keep in mind the memory needs.

NUMA Configurations

Non-Uniform Memory Access (NUMA) is a mechanism designed to improve performance on multiprocessor systems. The NUMA architecture allocates specific areas of memory to individual processors in a multiprocessor system so that processors experience less contention over access to large shared memory.

Analysis Services Multi-Dimensional instances are NUMA node aware. You can use the PerNumaNode setting to specify NUMA node behavior for multidimensional instances in the following ways:

PerNumaNode
-1	This setting enables SSAS to automatically adjust NUMA node behavior. This means that on systems with less than four NUMA nodes, then the SSAS instance will behave in the same way as setting PerNumaNode=0 and for systems with four or more NUMA nodes, the instance will behave in the same way as setting PerNumaNode=1. This is the default setting.
0	The SSAS instance will ignore NUMA nodes and create one IOProcess thread pool which will be used by all processors.
1	The SSAS instance will create one IOProcess thread pool for each NUMA node. This improves coordinated access to memory.
2	The SSAS instance will create one IOProcess thread pool for each logical processor. It is intended for highly scaled hardware, with intensive Analysis Services workloads.

Tabular is NUMA aware since SQL Server 2016 SP1.

NUMA node awareness and changes to behavior are automatically activated on systems with four or more NUMA nodes, where a separate job queue is maintained for each NUMA node. On systems with only two NUMA nodes, the marginal benefit usually doesn’t overcome the overhead of managing NUMA.

Storage Configurations

Although SSAS is a memory intensive service, during cube or tabular processing, storage will play a vital role. It depends on how frequently the cube is updated, but cube updates are periodic and often occur outside of operational hours. For SSAS most variants of RAID storage are suitable from a performance perspective. However, production environments should also consider requirements for resiliency. It is recommended that RAID 1, RAID 5, or another RAID variant, that offers some form of redundancy, is used to protect the cube data from disk error or loss.

In addition to the RAID configurations, there are few other items that should be considered:

Rather than having multiple volumes, it is recommended to have a single large separate volume for data files for SSAS cubes.
Data, TempDir, and backup folders of SSAS should be excluded from anti-virus scanners.
The files in cube data folders can become very fragmented over time. Defragmentation should be done to the cube volume periodically.

As a large number of SSAS cubes or tabulars can be created in one SSAS server, it is essential to look at the storage requirements for those databases as well. Ideally, the TempDir should be on a different drive and for SSAS cubes which have high writes and reads it is recommended to have a separate drive.

SSAS backups are not often taken by administrators as SSAS databases are not the original data source. However, it is a best practice to take SSAS backups so that it is easy to recover when the need arises. When the SSAS database is backed up, it will be the same size as the database. There is a compression option for SSAS backups and it will reduce backups by 20% when the backup is compressed.

Performance Counters

Performance Monitor (aka Permon) is a very handy tool to determine whether you need additional resources. Perfmon can be utilized to verify the resource requirements. Here are some counters you can monitor.

CPU

[Windows] Processor: % Processor Time
[Windows] Process (msmdsrv): % Processor Time
[Windows] System: Processor Queue Length
[SSAS] Threads: Query pool job queue length
[SSAS] Threads: Processing pool job queue length
[SSAS] Threads: Processing pool I/O job queue length

MEMORY

[SSAS] Memory: Memory Usage KB
[Windows] Process: Working Set (msmdsrv)
[Windows] Memory: Pages/sec
[Windows] Memory: Available MBytes
[Windows] Memory: Free & Zero Page List Bytes
[Windows] Memory: Cache Bytes
[Windows] Memory: Standby Cache Normal Priority Bytes
[SSAS] Memory: Memory Limit Low KB
[SSAS] Memory: Memory Limit High KB
[SSAS] Memory: Memory Limit Hard KB
[SSAS] Memory: Memory Limit VertiPaq KB
[SSAS] Memory: Cleaner Memory Shrinkable KB
[SSAS] Memory: Cleaner Memory Nonshrinkable KB
[SSAS] Memory: Cleaner Current Price

DISK

[Windows] LogicalDisk: Avg Disk Sec/Transfer
[Windows] LogicalDisk: Avg Disk Sec/Read
[Windows] LogicalDisk: Avg Disk Sec/Write
[Windows] LogicalDisk: Disk Bytes/Sec
[Windows] LogicalDisk: Disk Read Bytes/Sec
[Windows] LogicalDisk: Disk Write Bytes/Sec
[Windows] Process (msmdsrv): IO Data Bytes / Sec
[Windows] Process (msmdsrv): IO Read Bytes / Sec
[Windows] Process (msmdsrv): IO Write Bytes / Sec
[Windows] LogicalDisk: Free Megabytes
[Windows] LogicalDisk: % Free Space
NETWORK
[Windows] Network Interface: Bytes Total/Sec
[Windows] Network Interface: Bytes Sent/Sec
[Windows] Network Interface: Bytes Received/Sec

OTHER

[SSAS] Connections: Current User Sessions
[SSAS] Connections: Current Connections
[SSAS] Proc Aggregations: Rows Created/Sec
[SSAS] Proc Indexes: Rows/Sec
[SSAS] Storage Engine Query: Calculation cache hits/Sec
[SSAS] Storage Engine Query: Calculation cache lookups/Sec
[SSAS] Storage Engine Query: Flat cache hits/Sec
[SSAS] Storage Engine Query: Flat cache lookups/Sec

Next Steps

For further details please visit the following links:

About the author

Dinesh Asanka is a 10 time Data Platform MVP and frequent speaker at local and international conferences with more than 12 years of database experience.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

SQL Server Analysis Services SSAS Hardware Configuration Recommendations