Database VertiPaq Size

Summary

The amount of VertiPaq memory consumed by SSAS tabular database may grow over time while different portions of data are continuously added from designated data sources.

To be aware of your VertiPaq memory consumption rates, you can use this monitor to track which databases consume most of your VertiPaq capacity.

The monitor reports a warning when the amount of VertiPaq memory consumed by the database exceeds the configured 'Warning Threshold' override expressed as an absolute value in GB.

In cases when tabular databases consume more VertiPaq memory than it is allowed by the 'Critical Threshold' override, the monitor throws a critical alert.

Note that it is recommended to change the default threshold value to some significant number when enabling this monitor.

Causes

Database can approach or exceed the VertiPaq Memory setting under some circumstances. In some cases, a single operation requires memory beyond this limit, and despite the server’s best effort to clear memory from other less immediate tasks, allocations may sometimes necessarily exceed the configured limits, or else the operations must be forced to fail.

This can occur while processing large dimensions or fact data, particularly if there are attributes with data types consuming large amounts of space (e.g. long strings, binary data, etc.) present, or if the dimension contains numerous members. In addition, queries executed on the server may cover very large spaces, particularly if multiple dimensions are being cross-joined, or significant calculations are required to derive results for cells not aggregated previously by a wise administrator, or simply not possible to aggregate in advance.

Resolutions

The resolution may vary depending on what has caused high VertiPaq memory consumption. Make sure to thoroughly investigate each occurrence to determine the real causes. There are numerous approaches administrators can use to fix the issue. Upon considering the underlying activity causing the memory usage, requirements for workload on the server, available resources, and other factors, or consulting Microsoft support, you can do the following:

• Identify the sessions responsible for the most significant memory allocation and manually terminate them.

• Identify the session responsible for the most significant memory allocations and address them on a case-by-case basis to reschedule processing of jobs and reports, pre-allocate with cache warming queries during the periods of lower utilization or by designing specific aggregations in advance to be built during the processing and hence reducing memory consumption by user queries subsequently.

• Increase physical memory on the host computer.

• Decrease the memory usage by other processes on the host computer, such as instances of the SQL Server relational database engine or other SSAS instances.

• Increase the 'Total Memory Limit' setting or configured thresholds for the alert to avoid the condition.

• Disable the monitor entirely if the memory usage is not a concern on the server.

Overridable Parameters