sp_iqsysmon procedure

Function

Monitors multiple components of Sybase IQ, including the management of buffer cache, memory, threads, locks, I/O functions, and CPU utilization.

Batch mode syntax

sp_iqsysmon start_monitor
sp_iqsysmon stop_monitor [, “section(s)” ]
or
sp_iqsysmontime-period” [, “section(s)” ]

File mode syntax

sp_iqsysmon start_monitor, ‘filemode’ [, ”monitor-options” ]
sp_iqsysmon stop_monitor

Permissions

DBA authority required. Users without DBA authority must be granted EXECUTE permission to run the stored procedure.

Batch mode usage

start_monitor Starts monitoring.

stop_monitor Stops monitoring and displays the report.

time-period The time period for monitoring. Must be in the form HH:MM:SS.

section(s) The abbreviation for one or more sections to be displayed by sp_iqsysmon. When more than one section is specified, the section abbreviations must be separated by spaces and the list must be enclosed in single or double quotes. The default is to display all sections.

For the sections related to IQ store, you can specify main or temporary store by prefixing the section abbreviation with “m” or “t”, respectively. See Table 7-56. Without the prefix, both stores are monitored. For example, if you specify “mbufman”, only the IQ main store buffer manager is monitored. If you specify “mbufman tbufman” or “bufman”, both the main and temporary store buffer managers are monitored.

Table 7-56: sp_iqsysmon report section abbreviations

Report section or IQ component

Abbreviation

Buffer manager

(m/t)bufman

Buffer pool

(m/t)bufpool

Prefetch management

(m/t)prefetch

Free list management

(m/t)freelist

Buffer allocation

(m/t)bufalloc

Memory management

memory

Thread management

threads

CPU utilization

cpu

Transaction management

txn

Server context statistics

server

Catalog statistics

catalog

NoteThe Sybase IQ components Disk I/O and lock manager are not currently supported by sp_iqsysmon.

File mode usage

start_monitor Starts monitoring.

stop_monitor Stops monitoring and writes the remaining output to the log file.

filemode Specifies that sp_iqsysmon is running in file mode. In file mode, a sample of statistics is displayed for every interval in the monitoring period. By default, the output is written to a log file named dbname.connid-iqmon. Use the file_suffix option to change the suffix of the output file. See the monitor_options parameter for a description of the file_suffix option.

monitor_options The monitor _options string can include one or more of the following options:

Usage syntax examples

Table 7-57: sp_iqsysmon usage examples

Syntax

Result

sp_iqsysmon start_monitor

sp_iqsysmon stop_monitor

Starts the monitor in batch mode and displays all sections for main and temporary store

sp_iqsysmon start_monitor

sp_iqsysmon stop_monitor “mbufman mbufpool”

Starts the monitor in batch mode and displays the Buffer Manager and Buffer Pool statistics for main store

sp_iqsysmon “00:00:10”, “mbufpool memory”

Runs the monitor in batch mode for 10 seconds and displays the consolidated statistics at the end of the time period

sp_iqsysmon start_monitor, ‘filemode’, “-interval 5 -sections mbufpool memory”

sp_iqsysmon stop_monitor

Starts the monitor in file mode and writes to the log file every 5 seconds the statistics for Main Buffer Pool and Memory Manager

Description

The sp_iqsysmon stored procedure monitors multiple components of Sybase IQ, including the management of buffer cache, memory, threads, locks, I/O functions, and CPU utilization.

The sp_iqsysmon procedure supports two modes of monitoring:

Batch mode examples

Prints monitor information after 10 minutes:

sp_iqsysmon “00:10:00”

Prints only the Memory Manager section of the sp_iqsysmon report after 5 minutes:

sp_iqsysmon “00:05:00”, memory

Starts the monitor, executes two procedures and a query, stops the monitor, then prints only the Buffer Manager section of the report:

sp_iqsysmon start_monitor
go
execute proc1
go
execute proc2
go
select sum(total_sales) from titles
go
sp_iqsysmon stop_monitor, bufman
go

Prints only the Main Buffer Manager and Main Buffer Pool sections of the report after 20 minutes:

sp_iqsysmon “00:02:00”, “mbufman mbufpool”

File mode examples

Truncates and writes information to the log file every 2 seconds between starting the monitor and stopping the monitor:

sp_iqsysmon start_monitor, ‘filemode’, ‘-interval 2’
.
.
.
sp_iqsysmon stop_monitor

Appends output for only the Main Buffer Manager and Memory Manager sections to an ASCII file with the name dbname.connid-testmon. For the database iqdemo, writes results in the file iqdemo.2-testmon:

sp_iqsysmon start_monitor, ‘filemode’,
“-file_suffix testmon -append -section mbufman memory”
.
.
.
sp_iqsysmon stop_monitor

Example

Run the monitor in batch mode for 10 seconds and display the consolidated statistics at the end of the time period

sp_iqsysmon “00:00:10”, “mbufpool memory”

==============================
Buffer Pool (Main)
==============================
STATS-NAME         TOTAL  NONE  BTREEV  BTREEF  BV  VDO  DBEXT  DBID  SORT
MovedToMRU         0      0     0       0       0   0    0      0     0
MovedToWash        0      0     0       0       0   0    0      0     0
RemovedFromLRU     0      0     0       0       0   0    0      0     0
RemovedFromWash    0      0     0       0       0   0    0      0     0
RemovedInScanMode  0      0     0       0       0   0    0      0     0

STORE  GARRAY  BARRAY  BLKMAP  HASH  CKPT  BM  TEST  CMID  RIDCA  LOB
0      0       0       0       0     0     0   0     0     0      0
0      0       0       0       0     0     0   0     0     0      0
0      0       0       0       0     0     0   0     0     0      0
0      0       0       0       0     0     0   0     0     0      0
0      0       0       0       0     0     0   0     0     0      0

STATS-NAME                     VALUE 
Pages                          127   ( 100.0 %) 
InUse                          4     ( 3.1 %) 
Dirty                          1     ( 0.8 %) 
Pinned                         0     ( 0.0 %) 
Flushes                        0 
FlushedBufferCount             0 
GetPageFrame                   0 
GetPageFrameFailure            0 
GotEmptyFrame                  0 
Washed                         0
TimesSweepersWoken             0                                                                                                   washTeamSize                   0
WashMaxSize                    26   ( 20.5 %)
washNBuffers                   4     ( 3.1 %) 
washNDirtyBuffers              1     ( 0.8 %)                                                                                    washSignalThreshold            3      ( 2.4 %) 
washNActiveSweepers            0
washIntensity                  1 

============================== 
Memory Manager 
============================== 
STATS-NAME                   VALUE 
MemAllocated                 43616536     (  42594 KB) 
MemAllocatedMax              43735080     (  42710 KB) 
MemAllocatedEver             0            (      0 KB) 
MemNAllocated                67079
MemNAllocatedEver            0
MemNTimesLocked              0
MemNTimesWaited              0            (   0.0 %) 

See also

IQ UTILITIES statement in Chapter 1, “SQL Statements,” in Reference: Statements and Options

Chapter 5, “Monitoring and Tuning Performance” in the Performance and Tuning Guide