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.