Optimizing number of open indexes

If the default value of number of open indexes is insufficient, SAP ASE displays a message after trying to reuse active index descriptors, and you must adjust this value.

  1. Use sp_countmetadata to find the total number of index metadata descriptors:
    sp_countmetadata "open indexes"

    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.

    Suppose SAP ASE reports the following information:
    There are 698 user indexes in all database(s),
    requiring 286.289 Kbytes of memory. The 'open 
    indexes' configuration parameter is currently set to 
  2. Configure the number of open indexes parameter to 698:
    sp_configure "number of open indexes", 698

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

  3. During a peak period, find the number of active index metadata descriptors:
    sp_monitorconfig "open indexes"
    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                    182        516     73.92          590
             0                  NULL

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

    See sp_monitorconfig in the Reference Manual: Procedures.

  4. Configure the number of open indexes configuration parameter to 590, plus additional space for 10 percent more (59), for a total of 649:
    sp_configure "number of open indexes", 649

    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.