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
None required.
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 10-42. Without the prefix, both stores are monitored. For example, if you specify “mbufman”, only the Main IQ Store buffer manager is monitored. If you specify “mbufman tbufnam” or “bufman”, both the Main and Temporary Store buffer managers are monitored.
Report section or IQ component |
Abbreviation |
|---|---|
Buffer manager |
(m/t)bufnam |
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.
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 10-42 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.
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 |
Chapter 6, “Monitoring and Tuning Performance” in the Sybase IQ Performance and Tuning Guide
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.
Note that 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.
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”
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 asiqdemo, writes results in the file asiqdemo.2-testmon:
sp_iqsysmon start_monitor, ‘filemode’, “-file_suffix testmon -append -section mbufman memory” . . . sp_iqsysmon stop_monitor
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 %)
