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.
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.
There are 698 user indexes in all database(s), requiring 286.289 Kbytes of memory. The 'open indexes' configuration parameter is currently set to 500.
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.
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.
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.