Share via

Understanding Memory configuration in BI Semantic model

  • Article

Memory is by far the most critical resource for tabular model. Being able to monitor how much memory is being used and being able to configure it optimally is important in ensuring predictable performance.

 

First and foremost, let us understand how memory is used by SSAS.

  • Processing: During processing, SSAS needs memory to load data and create dictionaries and related data structures before it flushes them to disk. In addition, if the database being processed already contains some data, it must hold the previous version of the database until the transaction commits and the new database is ready for query.
  • Querying: During a query, SSAS sometimes need memory to hold temporary data structures that are needed to resolve the query.

SSAS configuration file msndsrv.ini stores memory settings for Tabular model. The easiest way to view and change these setting is by opening Analysis Services properties page inside SSMS.

Following screen shows the default values of these memory settings.

 

 

 

VertipaqPagingPolicy: This is a Boolean property with 0 and 1 as possible values. In mode 0, all xVelocity data is locked into memory, whereas in mode 1, xVelocity in-memory engine pages data to disk if the system is running out of memory. More specifically, in mode 1, only hash dictionaries are locked in memory; data pages can be flushed to disk which enables xVelocity to use more memory than is available. Must keep in mind that paging has detrimental effect on performance. Default value of this property is, mode 1.

HardMemoryLimit: This is the maximum memory that SSAS can ever allocate. If SSAS exceeds the hard memory limit, the system aggressively kills active sessions. With VertipaqPagingPolicy in mode 0, it is also the limit for the maximum working set of the process. If HardMemoryLimit is set to 0, it will use a default value mid-way between HighMemoryLimit and total physical memory.

TotalMemoryLimit: If memory usage exceeds the TotalMemoryLimit, memory manager evicts all cached data that is not currently in use. TotalMemoryLimit should always be less than HardMemoryLimit. The default value 80% of physical memory or the virtual address space, whichever is less.

LowMemoryLimit: This is the point at which the system starts to clear caches out of memory. As memory usage increases above the LowMemoryLimit, SSAS becomes more aggressive about cycling cached data until it hits the total memory limit, at which point it evicts everything that is not pinned.

VertipaqMemoryLimit: If you choose mode 0, the VertipaqMemoryLimit defines the total amount of memory xVelocity is allowed to lock in the working set. In mode 1, it defines a limit for the physical memory that is used by xVelocity, allowing paging for the remaining memory above this limit.

If you want to reduce memory for an instance of SSAS, it makes sense to set VertipaqMemoryLimit to a value that is lower than LowMemoryLimit.

 

No memory cleaning happens below the LowMemoryLimit, and the aggressiveness increases as soon as the usage approaches TotalMemoryLimit. Above TotalMemoryLimit, SSAS gets committed to cleaning memory, even if panic mode starts only after HardMemoryLimit.

All limit values are expressed as numbers. If their value is less than 100, it is interpreted as percentage of the total server memory. If it has value greater than 100, it is interpreted as the number of bytes to allocate.

Let us use an example to illustrate this. Say memory limits for a particular SSAS tabular instance are configured as under:

(a)    TotalMemoryLimit = 120 GB

(b)    LowMemoryLimit = 110 GB

(c)    VertipaqMemoryLimit = 100 GB

At some stage, assume that xVelocity data structures are using 210 GB and the process‘s total memory usage is 215 GB. These usage counter are way above the TotalMemoryLimit, however, because VertipaPagingPolicy is set to 1, memory eviction in this case will not happen.

This is because,

Total xVelocity memory pinned = VertipaqMemoryLimit = 100 GB. Remaining 110 Gb will be paged to disk. Memory used by other SSAS processes = 215 GB – 210 Gb = 5 GB. Therefore, total memory used by SSAS which is pinned = 100 GB + 5 GB = 105 GB. Since this is below LowMemoryLimit, no memory eviction will happen.

Comments

  • Anonymous
    August 17, 2014
    In your example at the end, how do you know that the data structures are using 210Gb, is there a perfmon counter that can be used to check this? Thanks.