Using sp_monitorconfig

sp_monitorconfig displays metadata cache usage statistics for certain shared server resources.

These statistics include:

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 number of free memory pages
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
The name of the instance on which you run sp_monitorconfig, if you are running in a clustered environment, or NULL if you are not running in a clustered environment.

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:

sp_monitorconfig "number of open indexes"

Usage information at date and time: May 28 2010  1:12PM.
Name                      Num_free    Num_active  Pct_act  Max_Used
Reuse_cnt    Instance_Name
------------------------  ----------  ----------  -------  ----------
-----------  ------------------------------
number of open indexes           217         283    56.60         300
           0                               NULL

The maximum number of open indexes used since the server was last started is 300, even though SAP ASE is configured for 500. Therefore, you can reset the number of open indexes configuration parameter to 330, which accommodates the 300 maximum used index descriptors, and allows for 10 percent more.

You can also determine the current size of the procedure cache using sp_monitorconfig ''procedure cache size''. This parameter describes the amount of space the procedure cache is currently configured for and the most it has ever actually used. In this example, the procedure cache is configured for 20,000 pages:

sp_configure "procedure cache size"

Parameter Name                      DefaultMemory         Used
         Config Value          Run Value            Unit
         Type
------------------------------      -------------------   ----------
         -------------------   -------------------- --------------------
          -------------------- procedure cache size                              7000        43914
                       20000                 20000      memory pages(2k)
                       dynamic

However, when you run sp_montorconfig “procedure cache size”, you find that the maximum procedure cache ever used is 14241 pages, which means you can lower the run value of the procedure cache, saving memory:

sp_monitorconfig "procedure cache size"

Usage information at date and time: May 28 2010  1:35PM.
Name                      Num_free    Num_active  Pct_act  Max_Used
Reuse_cnt    Instance_Name
------------------------  ----------  ----------  -------  ----------
-----------  ------------------------------
procedure cache size           5878        14122    70.61       14241
         384                              NULL

View the number of free memory pages (Num_free) to determine if your kernel resource memory configuration is adequate:

sp_monitorconfig "kernel resource memory"

Usage information at date and time: Oct 12 2010  1:35PM.
Name                      Num_free    Num_active  Pct_act  Max_Used
Reuse_cnt    Instance_Name
------------------------  ----------  ----------  -------  ----------
-----------  ------------------------------
kernel resource memory          9512         728     7.11       728
         0                              NULL

If the number of free pages is low, but the percent active (Pct_act) is high, you may need to increase the value of kernel resource memory.