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_iqsysmon “time-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-58. 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-58: 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

The 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:

-interval seconds Specifies the reporting interval in seconds. A sample of monitor statistics is output to the log file after every interval. The default is every 60 seconds, if the -interval option is not specified. The minimum reporting interval is 2 seconds. If the interval specified for this option is invalid or less than 2 seconds, the interval is set to 2 seconds.

The first display shows the counters from the start of the server. Subsequent displays show the difference from the previous display. You can usually obtain useful results by running the monitor at the default interval of 60 seconds during a query with performance problems or during a time of day with performance problems. A very short interval may not provide meaningful results. The interval should be proportional to the job time; 60 seconds is usually more than enough time.

-file_suffix suffix Creates a monitor output file named dbname.connid-suffix. If you do not specify the -file_suffix option, the suffix defaults to iqmon. If you specify the -file_suffix option and do not provide a suffix or provide a blank string as a suffix, no suffix is used.

-append or -truncate Directs sp_iqsysmon to append to the existing output file or truncate the existing output file, respectively. Truncate is the default. If both options are specified, the option specified later in the string is effective.

-section section(s) Specifies the abbreviation of one or more sections to write to the monitor log file. The default is to write all sections. The abbreviations specified in the sections list in file mode are the same abbreviations used in batch mode. See Table 7-58 for a list of abbreviations. When more than one section is specified, spaces must separate the section abbreviations.

If the -section option is specified with no sections, none of the sections are monitored. An invalid section abbreviation is ignored and a warning is displayed in the IQ message file.

Usage syntax examples

**Table 7-59: 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

In batch mode, sp_iqsysmon collects the monitor statistics for the period between starting and stopping the monitor or for the time period specified in the time-period parameter. At the end of the monitoring period, sp_iqsysmon displays a list of consolidated statistics.

sp_iqsysmon in batch mode is similar to the Adaptive Server Enterprise procedure sp_sysmon.
File mode

In file mode, sp_iqsysmon writes the sample statistics in a log file for every interval period between starting and stopping the monitor.

The first display in file mode shows the counters from the start of the server. Subsequent displays show the difference from the previous display.

sp_iqsysmon in file mode is similar to the IQ UTILITIES command START MONITOR and STOP MONITOR interface.

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