System Procedures
------------------------------------------------------------------
--------------
sp_monitorconfig
Description
Displays cache usage statistics regarding
metadata descriptors for indexes, objects, and databas.
sp_monitorconfig also reports statistics on auxiliary scan descriptors
ud 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¡± ction.
Specifying all displays descriptor help information for all indexes,
objects, databas, and auxiliary scan descriptors in the rver.
¡°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 t of values for the configname that you
specify. The values are:
config_val ¨C reports the configured value
system_val ¨C reports the systems default value when there's novalue
configured
total_val ¨C reports the actual value ud
Examples
创意表白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 databas 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_Ud Reud------------------------ --------
----------- ------- -------- ------
additional network memory 0 0 0.00 0 NA
audit queue size 100 0 0.00 0 NA
heap memory per ur 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 databas 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 ur connection 23 2 8.00 2 NA
number of ur process 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
ud 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_Ud Reud
-
------------- -------- ---------- ------- --------
------
number of open 217 283 56.60 300 No
You can then ret the size to 330, for example, to accommodate the
300 maximum ud 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_Ud Reud
-------------- -------- ---------- -------- --------
------
number of open 556 44 7.33 44 No
You can ret 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 u 200. U the number of aux scan descriptors
configuration parameter to ret the value to at least 32. A safe tting
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_Ud Reud
-------------- -------- ---------- -------- --------
------
number of aux s 170 30 15.00 32 No
Example 6
Adaptive Server is configured for five open databas, all of which
have been ud in the current ssion.
sp_monitorconfig ";number of open databas";
Usage information at date and time: Apr 22 2002 2:49PM.
Name num_free num_active pct_act
Max_Ud Reud
-------------- -------- ---------- -------- --------
------
number of open 0 5 100.00 5 Yes
However, as indicated by the Reud column, an additional databa needs
to be opened. If all 5 databas are in u, an error may result, unless
the descriptor for a databa that is not in u can be reud. To prevent
an error, ret number of open databas to a higher value.
Example 7
Only 10.2 percent of the transaction descriptors are currently being
ud. However, the maximum number of transaction descriptors ud 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_Ud Reud
-------------- -------- ---------- -------- --------
------
number of open 784 80 10.20
523 NA
Example 8
Using the optional parameter result_tbl_name to create a ur 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_Ud int, Reu_cnt int,
Date varchar(30))
The name of the table created becomes the cond parameter of
sp_monitorconfig:
sp_monitorconfig ";number of alarms";, sample_table
--------------
婴儿身高体重表(return status = 0)
lect * from sample_table
---------------
Name Num_free Num_active Pct_act
Max_Ud Reu_cnt Date
五十岁生日------------ -------- ---------- ------- -------- --------- -------
