Summary information |
|
|---|---|
Default value |
500 |
Range of values |
100–2147483647 |
Status |
Dynamic |
Display level |
Basic |
Required role |
System Administrator |
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, you can calculate an estimated metadata cache size by using sp_helpconfig. 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.
The default run value is 500. If this number is insufficient, Adaptive Server displays a message after trying to reuse active index descriptors, and you will need to adjust this value.
In order to configure the number of open indexes parameter optimally, perform the following steps:
Use sp_countmetadata to find the total number of index metadata descriptors. For example:
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 as follows:
sp_configure "number of open indexes", 698
This new configuration is only a start; the ideal size should be based 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. For example:
sp_monitorconfig "open indexes"
Usage information at date and time: Apr 22 2002 2:49PM. Name num_free num_active pct_act Max_Used Reused -------------- -------- --------- -------- -------- ------ number of open 182 516 73.92 590 No
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 for more information.
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, run sp_monitorconfig periodically. You will need to reset the cache size as the number of active descriptors changes.
