Using sp_monitorconfig to find metadata cache usage statistics

sp_monitorconfig displays metadata cache usage statistics on certain shared server resources, including:

For example, suppose the number of open indexes configuration parameter is 500. During a peak period, you can run sp_monitorconfig 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
Reuse_cnt   Instance_Name
------------------------   ----------  ----------  -------  -----------
----------  --------------------
number of open                    217         283    56.60          300
         0                  NULL

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,' which describes the amount of space in the procedure cache and the most space 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                   20000      3271

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 you can save memory by lowering the run value of the procedure cache:

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
Reuse_cnt   Instance_Name
------------------------   ----------  ----------  -------  -----------
----------  --------------------
procedure cache                  5878       14122    70.61        14241
          0                    NULL