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, SQL Server Administration |
number of open objects sets the maximum number of objects that can be open simultaneously on Adaptive Server.
If you are planning to make a substantial change, such as loading databases with a large number of objects from another server, use sp_helpconfig to recalculate 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 object metadata descriptor represents the state of an object while it is in use, or cached between uses.
Optimizing number of open objects
If the default number of open objects is insufficient, Adaptive Server displays a message after trying to reuse active object descriptors.
Use sp_countmetadata to find the total number of object metadata cache descriptors:
sp_countmetadata "open objects"
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 this information:
There are 696 user objects in all database(s), requiring 1166 Kbytes of memory. The 'open objects' configuration parameter is currently set to 500.
Configure number of open objects to account for the number of open objects:
sp_configure "number of open objects", 1407
1407 covers the 1340 user objects, plus 5 percent to accommodate temporary tables.
This new configuration is only a starting point; base the ideal number on the active object metadata cache descriptors, not the total number of objects.
During a peak period, find the number of active metadata cache descriptors:
sp_monitorconfig "open objects"
Usage information at date and time: Aug 20 2007 1:32PM.. Name Num_free Num_active Pct_act Max_Used Reuse_cnt Instance_Name --------------------- -------- ---------- ------- -------- --------- -------------------- number of open objects 560 847 71.40 1497 0 NULL
In this example, 1497 is the maximum number of object descriptors that have been used since the server was last started.
Configure the number of open objects to 1397, plus 10 percent (140), for a total of 1537:
sp_configure "number of open objects", 1537
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. See sp_monitorconfig in the Reference Manual: Procedures.