There are numerous changes and corrections to sp_monitorconfig. Replace the reference pages in Reference Manual: Procedures for sp_monitorconfig with this text.
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" [ , “result_tbl_name”] [, “full”]
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” – is the name of the table you create, in which to save the stored procedure results. This is an optional parameter.
“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 no value configured.
total_val – reports the actual value used.
Example 1 Shows all items that are open:
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
Example 2 Shows the status for all configurations:
sp_monitorconfig "all" -------------
Usage information at date and time: Oct 25 2002 10:36AM. Name num_free num_active pct_act Max_Used Reused ------------------------ -------- ----------- ------- -------- ------ additional network memory 0 0 0.00 0 NA audit queue size 100 0 0.00 0 NA heap memory per user 4096 0 0.00 0 No max cis remote connection 0 0 0.00 0 NA max memory 12404 21388 63.29 21388 NA max number network listen 3 2 40.00 2 NA max online engines 4 1 20.00 1 NA memory per worker process 1024 0 0.00 0 NA number of alarms 31 9 22.50 9 NA number of aux scan descri 200 0 0.00 0 NA number of devices 9 1 10.00 1 NA number of dtx participant 500 0 0.00 0 NA number of java sockets 0 0 0.00 0 NA number of large i/o buffers 6 0 0.00 0 NA number of locks 4673 327 6.54 408 NA number of mailboxes 30 0 0.00 0 NA number of messages 64 0 0.00 0 NA number of open databases 6 6 50.00 6 No number of open indexes 492 8 1.60 8 No number of open objects 482 18 3.60 18 No number of open partitions 447 53 10.60 0 NA number of remote connections 20 0 0.00 0 NA number of remote logins 20 0 0.00 0 NA number of remote sites 10 0 0.00 0 NA number of sort buffers 500 0 0.00 9 NA number of user connection 23 2 8.00 2 NA number of user processes 0 0 0.00 0 NA partition groups 1024 0 0.00 0 NA permission cache entries 15 0 0.00 0 NA procedure cache size 2567 704 21.52 810 No size of global fixed heap 150 0 0.00 0 NA size of process object heap 1500 0 0.00 0 NA size of shared class heap 1536 0 0.00 0 NA size of unilib cache 0 0 0.00 0 NA txn to pss ratio 16 0 0.00 0 NA (return status = 0)
Example 3 Shows 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
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 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
Example 5 Shows the number of active scan descriptors as 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
Example 6 Adaptive Server is configured for five 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 must 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 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
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), Num_free int, Num_active int, Pct_act char(6), Max_Used int, Reuse_cnt int, Date varchar(30))
The name of the table created becomes the second parameter of sp_monitorconfig:
sp_monitorconfig "number of alarms", sample_table
-------------- (return status = 0)
select * from sample_table
--------------- Name Num_free Num_active Pct_act Max_Used Reuse_cnt Date ------------ -------- ---------- ------- -------- --------- ------- number of alarms 29 11 27.50 11 -1 Dec 4 2002 10:20AM (1 row affected)
sp_monitorconfig "number of devices", sample_table
--------------------- (return status = 0)
select * from sample_table
----------------- Name Num_free Num_active Pct_act MaxUsed Reuse_cnt Date ------------ --------- ---------- ------- ------- -------- ------- number of alarms 29 11 27.50 11 -1 Dec 4 2002 10:20AM number of devices 9 1 10.00 1 -1 Dec 4 2002 10:20AM (2 rows affected)
The result set saved to the table accumulates until you delete or truncate the table.
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 Num_Reuse ---------------------- --------------- ------------ ----------- ----------- ----------- ------- ----------- ----------- additional network memory 0 496156 496156 334844 161312 32.51 161312 0 audit queue size 100 0 100 100 0 0.00 0 0 disk i/o structures 256 0 256 256 0 0.00 55 0heap memory per user 4096 563 4096 4096 0 0.00 0 0 max cis remote connection 0 100 100 100 0 0.00 0 0 max memory 33792 0 33792 3452 30340 89.78 30340 0 max number network listen 5 0 5 4 1 20.00 1 0 max online engines 1 0 1 0 1 100.00 1 0 memory per worker process 1024 68 1024 1024 0 0.00 0 0 number of alarms 40 0 40 30 10 25.00 10 0 number of aux scan descri 200 25 200 200 0 0.00 0 0 number of devices 10 0 10 9 1 10.00 1 0 number of dtx participant 500 0 500 500 0 0.00 0 0 number of java sockets 0 50 50 50 0 0.00 0 0 number of large i/o buffe 6 0 6 6 0 0.00 1 0 number of locks 5000 334 5000 4905 95 1.90 446 0 number of mailboxes 30 1 30 29 1 3.33 1 0 number of messages 64 1 64 64 0 0.00 0 0 number of open databases 12 0 12 6 6 50.00 6 0 number of open indexes 500 0 500 481 19 3.80 45 0 number of open objects 500 0 500 135 365 73.00 367 0 number of open partitions 500 0 500 447 53 10.60 56 0 number of remote connecti 20 0 20 20 0 0.00 0 0 number of remote logins 20 0 20 20 0 0.00 0 0 number of remote sites 10 0 10 10 0 0.00 0 0 number of sort buffers 500 0 500 500 0 0.00 9 0 number of user connection 25 0 25 24 1 4.00 3 0 number of worker processe 0 0 0 0 0 0.00 0 0 partition groups 1024 0 1024 1024 0 0.00 0 0 permission cache entries 15 0 15 15 0 0.00 0 77 procedure cache size 3271 0 4727 0 4727 100.00 8225 277 size of global fixed heap 150 0 150 150 0 0.00 0 0 size of process object he 1500 0 1500 1500 0 0.00 0 0 size of shared class heap 1536 0 1536 1536 0 0.00 0 0 size of unilib cache 0 119386 119386 118922 464 0.39 464 0 txn to pss ratio 16 0 16 400 0 0.00 0 0 (return status = 0)
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 Adaptive 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.
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; add 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 must 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 sy_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.
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.
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 Adaptive Server under heavy load might report numbers in the sp_monitorconfig output that are not 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.
Only a System Administrator can execute sp_monitorconfig.
System procedures sp_configure, sp_countmetadata, sp_helpconfig, sp_helpconstraint, sp_sysmon
