Monitors multiple components of Sybase IQ, including the management of buffer cache, memory, threads, locks, I/O functions, and CPU utilization.
sp_iqsysmon start_monitor sp_iqsysmon stop_monitor [, “section(s)” ] or sp_iqsysmon “time-period” [, “section(s)” ]
sp_iqsysmon start_monitor, ‘filemode’ [, ”monitor-options” ] sp_iqsysmon stop_monitor
DBA authority required. Users without DBA authority must be granted EXECUTE permission to run the stored procedure.
Parameter |
Description |
|---|---|
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. 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. |
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 |
Parameter |
Description |
|---|---|
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 |
The monitor _options string can include one or more options:
monitor_options string option |
Description |
|---|---|
-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. 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. |
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 |
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.
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.
sp_iqsysmon “00:10:00”
sp_iqsysmon “00:05:00”, memory
sp_iqsysmon start_monitor go execute proc1 go execute proc2 go select sum(total_sales) from titles go sp_iqsysmon stop_monitor, bufman go
sp_iqsysmon “00:02:00”, “mbufman mbufpool”
sp_iqsysmon start_monitor, ‘filemode’, ‘-interval 2’ . . . sp_iqsysmon stop_monitor
sp_iqsysmon start_monitor, ‘filemode’, “-file_suffix testmon -append -section mbufman memory” . . . sp_iqsysmon stop_monitor
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 %)