sp_monitorconfig displays metadata cache usage statistics on certain shared server resources, including:
The number of databases, objects, and indexes that can be open at any one time
The number of auxiliary scan descriptors used by referential integrity queries
The number of free and active descriptors
The percentage of active descriptors
The maximum number of descriptors used since the server was last started
The current size of the procedure cache and the amount actually used
For example, suppose you have configured the number of open indexes configuration parameter to 500. During a peak period, you can run sp_monitorconfig as follows to get an accurate reading of the actual metadata cache usage for index descriptors.
1> sp_monitorconfig "number of 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 217 283 56.60 300 No
In this report, the maximum number of open indexes used since the server was last started is 300, even though Adaptive Server is configured for 500. Therefore, you can reset the number of open indexes configuration parameter to 330, to accommodate the 300 maximum used index descriptors, plus space for 10 percent more.
You can also determine the current size of the procedure cache with sp_monitorconfig procedure cache size. This parameter describes the amount of space in the procedure cache is currently configured for and the most it has ever actually used. For example, the procedure cache in the following server is configured for 20,000 pages:
1> sp_configure "procedure cache size"
option_name config_value run_value ------------------------------ ------------ --------- procedure cache size 7000 7000
However, when you run sp_montorconfig “procedure cache size”, you find that the most the procedure cache has ever used is 14241 pages, which means that you can lower the run value of the procedure cache, saving memory:
1> sp_monitorconfig "procedure cache size"
Usage information at date and time: Apr 22 2002 2:49PM. Name num_free num_active pct_act Max_Used Reused -------------- -------- ---------- ------- -------- ------ procedure cache 5878 14122 70.61 14241 No
