Optimizing the number of open databases

If SAP ASE displays a message saying that you have exceeded the allowable number of open databases, adjust the value.

  1. Use sp_countmetadata to find the total number of database metadata descriptors:
    sp_countmetadata "open databases"

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

    Suppose SAP ASE reports the following information:
    There are 50 databases, requiring 1719 Kbytes of memory. The 'open databases' configuration parameter is currently set to 500.
  2. Configure number of open databases with the value of 50:
    sp_configure "number of open databases", 50

    This new configuration number is only a starting point; base the ideal size on the number of active metadata database cache descriptors, not the total number of databases.

  3. During a peak period, find the number of active metadata descriptors:
    sp_monitorconfig "open databases"
    Usage information at date and time: Apr 22 2002  2:49PM.
    Name                       Num_free    Num_active  Pct_act  Max_Used
    Reuse_cnt   Instance_Name
    ------------------------   ----------  ----------  -------  -----------
    ----------  --------------------
    number of open                     50          20      .00           26
             0                  NULL

    In this example, 20 metadata database descriptors are active; the maximum number of descriptors that have been active since the server was last started is 26.

    See sp_monitorconfig in the Reference Manual: Procedures for more information.

  4. Configure number of open databases to 26, plus additional space for 10 percent more (about 3), for a total of 29:
    sp_configure "number of open databases", 29

    If there is a lot of activity on the server, for example, if databases are being added or dropped, periodically run sp_monitorconfig. Reset the cache size as the number of active descriptors changes.