sp_monitor  sp_object_stats

Chapter 1: System Procedures

sp_monitorconfig

Description

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.

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

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:

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 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 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 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.

NoteIf 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)

Usage

Permissions

Only a System Administrator can execute sp_monitorconfig.

Auditing

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

Event

Audit option

Command or access audited

Information in extrainfo

38

exec_procedure

Execution of a procedure

  • 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

See also

System procedures sp_configure, sp_countmetadata, sp_helpconfig, sp_helpconstraint, sp_sysmon





Copyright © 2005. Sybase Inc. All rights reserved. sp_object_stats

View this book as PDF