sp_monitorconfig

Displays cache usage statistics regarding metadata descriptors for indexes, objects, databases, and the kernel resource memory pool. sp_monitorconfig also reports statistics on auxiliary scan descriptors used for referential integrity queries, and usage statistics for transaction descriptors and DTX participants.

Syntax

sp_monitorconfig "configname"[, "result_tbl_name"][, "full"]

Parameters

configname – is either all, or part of the configuration parameter name with the monitoring information that is being queried. Valid configuration parameters are listed in the “Usage” section. Specifying all displays descriptor help information for all indexes, objects, databases, and auxiliary scan descriptors in the server.
“result_tbl_name” – (optional) is the name of the table you create to save the stored procedure results. If you pass a table name for result_tabl_name that does not already exist, sp_monitorconfig creates a table to hold the result set.
“full” – returns a set of values for the configname that you specify. The values are:
- config_val – reports the configured value
- system_val – reports the systems default value when there’s novalue configured
- total_val – reports the actual value used

Examples

Example 1 – Shows all items that are open:

sp_monitorconfig "open"

Configuration option is not unique.
option_name                    config_value run_value  
-----------------------------   ----------  ----------
number of open databases                12           12
number of open objects                 500          500
curread change w/ open cursors           1            1
open index hash spinlock ratio         100          100
number of open indexes                 500          500
open index spinlock ratio              100          100
open object spinlock ratio             100          100
number of open partitions              500          500

Example 2 – Shows the status for all configurations:

sp_monitorconfig "all"

-------------
Usage information at date and time: May  6 2010  4:32PM.
Name                   Num_free Num_active Pct_act Max_Used Reuse_cnt Instance_Name
--------------------   --------- --------- -------  ------- --------- -------------
additional network memory  1358436  809440   37.34    825056        0           NULL
audit queue size               100       0    0.00         0        0           NULL
disk i/o structures            256       0    0.00        29        0           NULL
heap memory per user          4096       0    0.00         0        0           NULL

. . . 

size of process object he     3000       0    0.00          0        0          NULL
size of shared class heap     6144       0    0.00          0        0          NULL
size of unilib cache        306216     816    0.27        816        0          NULL
txn to pss ratio               400       0    0.00          0        0         NULL

Example 3 – Shows 61 active object metadata descriptors, with 439 free. The maximum used at a peak period since the SAP ASE server was last started is 61:

sp_monitorconfig "open objects"
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 objects        439      61    12.20        61        0           NULL

You can then reset the size to 550, for example, to accommodate the 439 maximum used metadata descriptors, plus space for 10 percent more:

sp_configure "number of open objects", 330

Example 4 – Shows the maximum number of index metadata descriptors, which is 44:

sp_monitorconfig "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 indexes        556     44      7.33        44        0           NULL

You can reset the size to 100, the minimum acceptable value:

sp_configure "number of open indexes", 100

Example 5 – Shows the number of active scan descriptors as 30, though the SAP ASE server is configured to use 200. Use the number of aux scan descriptors configuration parameter to reset the value to at least 32. A safe setting is 36, to accommodate the 32 scan descriptors, plus space for 10 percent more:

sp_monitorconfig "aux scan descriptors"
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 aux scan descri     170     30    15.00         32        0           NULL

Example 6 – The SAP ASE server is configured for five open databases, all of which have been used in the current session.
```
sp_monitorconfig "number of open databases"
Name                   Num_free Num_active Pct_act Max_Used Reuse_cnt Instance_Name
--------------------   --------- --------- -------  ------- --------- -------------
number of open databses         0      5    100.00         5  Yes              NULL
```
However, as indicated by the Reuse_cnt column, an additional database needs to be opened. If all 5 databases are in use, an error may result, unless the descriptor for a database that is not in use can be reused. To prevent an error, reset number of open databases to a higher value.

Example 7 – Only 10.2 percent of the transaction descriptors are currently being used. However, the maximum number of transaction descriptors used at a peak period since the SAP ASE server was last started is 523:

sp_monitorconfig "txn to pss ratio"
Usage information at date and time: Apr 22 2002  2:49PM.
Name                   Num_free Num_active Pct_act Max_Used Reuse_cnt Instance_Name
--------------------   --------- --------- -------  ------- --------- -------------
txn to pss ratio              784     80     10.20       523        0           NULL

Example 8 – Using the optional parameter result_tbl_name to create a user table saves the sp_monitorconfig result to this table:

create table sample_table
(Name varchar(35),Config_val int, System_val int, Total_val int,
Num_free int, Num_active int, Pct_act char(6), Max_used int,
Num_Reuse int, Date varchar(30))

create table sample_table
(Name varchar(35),
Config_val int,
System_val int,
Total_val int,
Num_free int,
Num_active int,
Pct_act char(6),
Max_Used int,
Reuse_cnt int,
Date varchar(30),
Instance_Name varchar(35))

The name of the table created becomes the second parameter of sp_monitorconfig. Capture the values for number of locks and number of alarms in sample_table:

sp_monitorconfig "locks", sample_table
sp_monitorconfig "number of alarms", sample_table

Display the values captured in sample_table:

select * from sample_table
 Name               Config_val System_val Total_val Num_free Num_active
Pct_act Max_used Reuse_cnt   Date                Instance_Name
------------------- ----------- ---------- --------- --------- ----------
------- -------- ---------   ------------------- ------------------------
 number of locks         5000        684      5000     4915         85
1.70        117         0    Aug 23 2006  6:53AM
 number of alarms             40       0        40       28         12
30.00        13         0    Aug 23 2006  6:53AM

The result set saved to the table accumulates until you delete or truncate the table.

Note: If sample_table is in another database, you must provide its fully qualified name in quotes.

Example 9 – Displays the configure_value, system_value, and run_value columns of all the configurations:

sp_monitorconfig "all", null, "full"
go
Usage information at date and time: Mar 23 2004  5:15PM
Name                    Configure Value  System Value  Run Value
Num_free   Num_active  Pct_act  Max_Used  Reuse_cnt   Instance_Name
------------------------     ----------     ----------      -------
---------  ----------  -------  --------  ---------   -------------------
additional network memory             0        2167876      2167876
  1358436      809440    37.34    825056          0                  NULL
audit queue size                    100              0          100
      100           0     0.00         0          0                  NULL
disk i/o structures                 256              0          256
      256           0     0.00        29          0                  NULL
heap memory per user               4096            563         4096  
     4096           0     0.00         0          0                  NULL
kernel resource memory             4096              0         4096
     3567         529    12.92       529          0                  NULL
max cis remote connection             0            100          100
      100           0     0.00         0          0                  NULL
. . .
size of shared class heap          6144              0         6144
     6144           0     0.00         0          0                  NULL
size of unilib cache            0307032         307032       306216
      816           0      .27       816          0                  NULL
txn to pss ratio                     16              0           16
      400           0     0.00         0           0                 NULL

Usage

There are additional considerations when using sp_monitorconfig:

The output for additional network memory reports the utilization statistics for the global network memory pool regardless of whether or not memory has been added to this pool by setting additional network memory to a positive value.
If the max cis remote connections configuration parameter has a config_value, the system_val reports a value of zero (0).
If you reconfigure a resource using a value that is smaller than the original value it was given, the resource does not shrink, and the Num_active configuration parameter can report a number that is larger than Total_val. The resource shrinks and the numbers report correctly when the SAP ASE server restarts.
sp_monitorconfig displays cache usage statistics regarding metadata descriptors for indexes, objects, and databases, such as the number of metadata descriptors currently in use by the server.
sp_monitorconfig also reports the number of auxiliary scan descriptors in use. A scan descriptor manages a single scan of a table when queries are run on the table.
sp_monitorconfig monitors the following resources:
- additional network memory
- audit queue size
- heap memory per user
- max cis remote connection
- max memory
- max number network listeners
- memory per worker process
- max online engines
- number of alarms
- number of aux scan descriptors
- number of devices
- number of dtx participants
- number of java sockets
- number of large i/o buffers
- number of locks
- number of mailboxes
- number of messages
- number of open databases
- number of open indexes
- number of open objects
- number of open partitions
- number of remote connections
- number of remote logins
- number of remote sites
- number of sort buffers
- number of user connections
- number of worker processes
- partition groups
- permission cache entries
- procedure cache size
- size of global fixed heap
- size of process object heap
- size of shared class heap
- size of unilib cache
- txn to pss ratio
The columns in the sp_monitorconfig output provide the following information:
- Num_free – specifies the number of available metadata or auxiliary scan descriptors not currently used.
- Num_active – specifies the number of metadata or auxiliary scan descriptors installed in cache (that is, active).
- Pct_active – specifies the percentage of cached or active metadata or auxiliary scan descriptors.
- Max_Used – specifies the maximum number of metadata or auxiliary scan descriptors that have been in use since the server was started.
- Reuse_cnt – specifies whether a metadata descriptor was reused in order to accommodate an increase in indexes, objects, or databases in the server. The returned value is Yes, No or NA (for configuration parameters that do not support the reuse mechanism, such as the number of aux scan descriptors).
Use the value in the Max_Used column as a basis for determining an appropriate number of descriptors; be sure to add about 10 percent for the final setting. For example, if the maximum number of index metadata descriptors used is 142, you might set the number of open indexes configuration parameter to 157.
If the Reused column states Yes, reset the configuration parameter to a higher value. When descriptors need to be reused, there can be performance problems, particularly with open databases. An open database contains a substantial amount of metadata information, which means that to fill up an open database, the SAP ASE server needs to access the metadata on the disk many times; the server can also have a spinlock contention problem. To check for spinlock contention, use the system procedure sp_sysmon. See the Performance and Tuning Series: Monitoring Adaptive Server with sp_sysmon. To find the current number of indexes, objects, or databases, use sp_countmetadata.
To get an accurate reading, run sp_monitorconfig during a normal SAP ASE peak time period. You can run sp_monitorconfig several times during the peak period to ensure that you are actually finding the maximum number of descriptors used.

result_tbl_name creates a table using the following syntax. All the result information is saved in this table, which returns no standard output.

create table table_name(
    Name varchar(35), Num_free int,
    Num_active int, Pct_act char(6),
    Max_Used int, Reuse_cnt int,
    Date varchar(30))

Some configuration parameters, such as number of sort buffers and txn to pss ratio, are dependent on the number of configured user connections, while other configuration parameters, such as max number of network listeners, are per engine.
For the configuration value permission cache entries, the values of Num_free, Num_active, Pct_act, and Max_Used are averages of per connection values, however Reuse_cnt is a server-wide value.
The output of sp_monitorconfig uses the number of user connections and online engines to calculate the values for the columns num_free, num_active, pct_act and max_used.
The updates on the internal monitor counters are done without using synchronization methods because of performance reasons. For this reason, a multi-engine SAP ASE server under heavy load might report numbers in the sp_monitorconfig output that are not a completely accurate.
You might see the number of active locks as greater than 0 on an idle system. These “active” locks are reserved and used internally.

Permissions

The permission checks for sp_monitorconfig differ based on your granular permissions settings.

Setting	Description
Enabled	With granular permissions enabled, you must be a user with mon_role or have `manage server` privileges.
Disabled	With granular permissions disabled, you must be a user with either mon_role or sa_role.

Information	Values
Event	38
Audit option	exec_procedure
Command or access audited	Execution of a procedure
Information in `extrainfo`	Roles – Current active roles Keywords or options – NULL Previous value – NULL Current value – NULL Other information – All input parameters Proxy information – Original login name, if set proxy in effect

Auditing

Values in event and extrainfo columns from the sysaudits table are: