RESOURCE_POOLS

Displays information about the parameters specified for the resource pool by CREATE RESOURCE POOL or ALTER RESOURCE POOL.

Note: Column names in the RESOURCE_POOL table mirror syntax in the CREATE RESOURCE POOL statement. Therefore, column names do not use underscores.

Column Name Data Type Description
NAME

VARCHAR

The name of the resource pool.
IS_INTERNAL

BOOLEAN

Denotes whether a pool is one of the Built-In Pools.
MEMORYSIZE

VARCHAR

Value of the amount of memory allocated to the resource pool.
MAXMEMORYSIZE

VARCHAR

Value assigned as the maximum size the resource pool could grow by borrowing memory from the GENERAL pool.

EXECUTIONPARALLELISM

INTEGER

Limits the number of threads used to process any single query issued in this resource pool
PRIORITY

INTEGER

Value of PRIORITY parameter specified when defining the pool.
RUNTIMEPRIORITY

VARCHAR

Value that indicates the amount of run-time resources (CPU, I/O bandwidth) the Resource Manager should dedicate to running queries in the resource pool. Valid values are:

  • HIGH
  • MEDIUM (default)
  • LOW

These values are relative to each other. Queries with a HIGH run-time priority are given more CPU and I/O resources than those with a MEDIUM or LOW run-time priority.

RUNTIMEPRIORITYTHRESHOLD

INTEGER

Limits in seconds how soon a query must finish before the Resource Manager assigns to it the resource pool's RUNTIMEPRIORITY setting.
QUEUETIMEOUT

INTEGER or INTERVAL

The interval or value in seconds of the QUEUETIMEOUT parameter specified when defining the pool. Represents the maximum amount of time the request is allowed to wait for resources to become available before being rejected.
PLANNEDCONCURRENCY

INTEGER

Value of PLANNEDCONCURRENCY parameter specified when defining the pool, which represents the preferred number of concurrently executing queries in the resource pool.

MAXCONCURRENCY

INTEGER

Value of MAXCONCURRENCY parameter specified when defining the pool, which represents the maximum number of concurrent execution slots available to the resource pool.

RUNTIMECAP

INTERVAL

The maximum time a query in the pool can execute.
SINGLEINITIATOR

BOOLEAN

Specifies whether all requests using this pool are issued against the same initiator node or multiple initiator nodes can be used. Included for backwards compatibility. For all built-in resource pools, this must be set to false for all user-defined pools.

CPUAFFINITYSET

VARCHAR

Value which represents the set of CPUs on which queries associated with this pool are executed. For example, '0, 2-4' for the 0, 2, 3, and 4 CPUs, or '25%' for a percentage of available CPUs. Percentage values are rounded down to whole CPUs.
CPUAFFINITYMODE

VARCHAR

The mode of the CPU affinity. If CPUAFFINITYSET is set to a CPU percentage or index/index list, then its value is one of the following:

  • SHARED: Pool is pinned to the CPU indexes or percentage defined in CPUAFFINITYSET, and other pools can share the same CPUs
  • EXCLUSIVE: Pool is pinned to the CPU indexs or percentage defined in CPUAFFINITYSET, but other pools cannot use the same CPUs.
  • ANY: Pool has no affinity for a specific CPU or percentage of available CPUs.
CASCADETO

VARCHAR

The name of the secondary resource pool, if one exists.

See Also