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 |
number of open indexes sets the maximum number of indexes that can be used simultaneously on Adaptive Server.
If you are planning to make a substantial change, such as loading databases with a large number of indexes from another server, use sp_helpconfig to calculate an estimated metadata cache size. sp_helpconfig displays the amount of memory required for a given number of metadata descriptors, as well as the number of descriptors that can be accommodated by a given amount of memory. An index metadata descriptor represents the state of an index while it is in use or cached between uses.
Optimizing number of open indexes
If the default value of number of open indexes is insufficient, Adaptive Server displays a message after trying to reuse active index descriptors, and you must adjust this value.
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 Adaptive Server 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 500.
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.
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.
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.
