Service Allocation Limit

In addition to the global allocation limit, each service running on the host has an allocation limit, the service allocation limit. Given that collectively, all services cannot consume more memory than the global allocation limit, each service has what is called an effective allocation limit. The effective allocation limit of a service specifies how much physical memory a service can in reality consume given the current memory consumption of other services.

What happens when the allocation limit is reached?

Memory is a finite resource. Once the allocation limit has been reached and the pool is exhausted, the memory manager can no longer allocate memory for internal operations without first giving up something else. Buffers and caches are released, and column store tables are unloaded, column by column, based on a least-recently-used order, up to a preset lower limit. When tables are partitioned over several hosts, this is managed on a host-by-host basis; that is, column partitions are unloaded only on hosts with an acute memory shortage.

Table (column or partition) unloading is generally not a good situation since it leads to performance degradation later when the data will have to be reloaded for queries that need them. You can identify pool exhaustion by examining the M_CS_UNLOADS system view.

However, it is still possible that the memory manager needs more memory that it is available. For example, when too many concurrent transactions use up all memory, or when a particularly complex query performs a cross join on very large tables and creates a huge intermediate result that exceeds the available memory. Such situations can potentially lead to an out-of-memory failure.