Displays cache usage statistics regarding metadata descriptors for indexes, objects, and databases. sp_monitorconfig also reports statistics on auxiliary scan descriptors used for referential integrity queries, and usage statistics for transaction descriptors and DTX participants.
sp_monitorconfig "configname"
is either all, or part of the configuration parameter name whose monitoring information 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.
sp_monitorconfig "open"
Configuration option is not unique. option_name config_value run_value ------------------------------ ------------ ----------- curread change w/ open cursors 1 1 number of open databases 12 12 number of open indexes 500 500 number of open objects 500 500 open index hash spinlock ratio 100 100 open index spinlock ratio 100 100 open object spinlock ratio 100 100
There are 283 active object metadata descriptors, with 217 free. The maximum used at a peak period since Adaptive Server was last started is 300:
sp_monitorconfig "open objects"
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
You can then reset the size to 330, for example, to accommodate the 300 maximum used metadata descriptors, plus space for 10 percent more:
sp_configure "number of open objects", 330
The maximum number of index metadata descriptors 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 Reused -------------- -------- ---------- -------- -------- ------ number of open 556 44 7.33 44 No
You can reset the size to 100, the minimum acceptable value:
sp_configure "number of open indexes", 100
The number of active scan descriptors is 30, though Adaptive 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 Reused -------------- -------- ---------- -------- -------- ------ number of aux s 170 30 15.00 32 No
Adaptive Server is configured for 5 open databases, all of which have been used in the current session.
sp_monitorconfig "number of open databases"
Usage information at date and time: Apr 22 2002 2:49PM. Name num_free num_active pct_act Max_Used Reused -------------- -------- ---------- -------- -------- ------ number of open 0 5 100.00 5 Yes
However, as indicated by the Reused 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.
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 Adaptive 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 Reused -------------- -------- ---------- -------- -------- ------ number of open 784 80 10.20 523 NA
Create a user table named #tmp_res_monitor, and save the sp_monitorconfig result to this table:
create table #tmp_res_monitor(Name varchar(35), Num_free int, Num_active int, Pct_act char(6), Max_Used int, Reuse_cnt int, Date varchar(30)) gosp_monitorconfig "number of locks", #tmp_res_monitor goselect * from #tmp_res_monitor go Name num_free num_active pct_act Max_Used Reused -------------- -------- ---------- -------- -------- ------ number of locks 4361 639 12.78 3288 -1 Apr 22 2002 10:06AM
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 |
memory per worker process |
number of locks |
number of remote logins |
procedure cache size |
audit queue size |
number of alarms |
number of mailboxes |
number of remote sites |
size of global fixed heap |
heap memory per user |
number of aux scan descriptors |
number of messages |
number of sort buffers |
size of process object heap |
max cis remote connection |
number of devices |
number of open databases |
number of user connections |
size of shared class heap |
max memory |
number of dtx participants |
number of open indexes |
number of worker processes |
size of unilib cache |
max number network listeners |
number of java sockets |
number of open objects |
partition groups |
txn to pss ratio |
max online engines |
number of large i/o buffers |
number of remote connections |
permission cache entries |
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.
Reused – 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,
Adaptive 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.
For more information, see the Performance and Tuning Guide.
To find the current number of indexes, objects, or databases, use sp_countmetadata.
To get an accurate reading, run sp_monitorconfig during a normal Adaptive Server 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.
Only a System Administrator can execute sp_monitorconfig.
System procedures sp_configure, sp_countmetadata, sp_helpconfig, sp_helpconstraint, sp_sysmon
