number of open partitions

number of open partitions specifies the number of partitions that SAP ASE can access at one time.

Summary Information

Default value

500

Range of values

100 – 2147483647

Status

Dynamic

Display level

Basic

Required role

System administrator

Configuration groups

Memory Use, Meta-Data Caches

Optimizing the number of open partitions parameter for your system

If the default value of number of open partitions is insufficient, SAP ASE displays a message after trying to reuse active partition descriptors. You must adjust this value.

This example assumes a system administrator has set the number of open partitions to 110:

  1. Use sp_countmetadata to confirm the total number of open partitions:
    sp_countmetadata "open partitions"
    There are 42 user partitions in all database(s),
    requiring 109 Kbytes of memory. The 'open 
    partitions' configuration parameter is currently set 
    to 110.

    The best time to run sp_countmetadata is when there is little activity in the server. Running sp_countmetadata during a peak time can cause contention with other processes.

  2. During a peak period, find the number of active metadata cache descriptors, for example:
    sp_monitorconfig "open partitions"
    Usage information at date and time: Jun 30 2008  3:15PM.
    
    Name                       Num_free    Num_active  Pct_act  Max_Used
    Reuse_cnt   Instance_Name
    ------------------------   ----------  ----------  -------  -----------
    ----------  --------------------
    number of open partitions     27               57     51.8           83
              0                   NULL

    In this example, 83 is the maximum number of partition descriptors that have been used since the server was last started.

  3. Configure the number of open partitions to 83, plus 10 percent (8), for a total of 91:
    sp_configure "number of open partitions", 91

If there is a lot of activity on the server, for example, if tables are being added or dropped, periodically run sp_monitorconfig. Reset the cache size as the number of active descriptors changes. See sp_monitorconfig in the Reference Manual: Procedures.