Monitors multiple components of SAP 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
See the Remarks section for a complete list of abbreviations.
If you specify more than one section, separate the section abbreviations using spaces, and enclose the list in single or double quotes. The default is to display all sections.
For sections related to the IQ main 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.
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 that generally has 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.
See the Remarks section for a complete list of abbreviations.
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 written to the IQ message file.
You must have EXECUTE privilege on the system procedure, as well as the MONITOR system privilege.
Report Sections or IQ Components to be Reported On | Abbreviation to Type |
---|---|
Buffer allocation |
(main) – mbufalloc (temporary) – tbufalloc |
Buffer manager |
(main) – mbufman (temporary) – tbufman |
Buffer pool |
(main) – mbufpool (temporary) – tbufpool |
Catalog statistics | catalog |
CPU utilization | cpu |
Free list management |
(main)– mfreelist (temporary) – tfreelist |
Memory management | memory |
Prefetch management |
(main)– mprefetch (temporary)– tprefetch |
IQ RLV In-Memory Store statistics | rlv |
Large Memory Allocator (LMA) statistics | lma |
Server context statistics | server |
Thread management | threads |
Transaction management | txn |
The sp_iqsysmon stored procedure monitors multiple components of SAP Sybase IQ, including the management of buffer cache, memory, threads, locks, I/O functions, and CPU utilization.
sp_iqsysmon in batch mode is similar to the SAP® Sybase Adaptive Server Enterprise procedure sp_sysmon.
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.
Example 1:
Starts the monitor in batch mode and displays all sections for the main and temporary stores:
sp_iqsysmon start_monitor sp_iqsysmon stop_monitor
Example 2:
Starts the monitor in batch mode and displays the Buffer Manager and Buffer Pool statistics for the main store:
sp_iqsysmon start_monitor sp_iqsysmon stop_monitor 'mbufman mbufpool'
Example 3:
Prints monitor information after 10 minutes:
sp_iqsysmon '00:10:00'
Example 4:
Prints only the Memory Manager section of the sp_iqsysmon report after 5 minutes:
sp_iqsysmon '00:05:00', memory
Example 5:
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
Example 6:
Prints only the Main Buffer Manager and Main Buffer Pool sections of the report after 2 minutes:
sp_iqsysmon '00:02:00', 'mbufman mbufpool'
Example 7:
Prints only the RLV sections of the report after 1 hour:
sp_iqsysmon '01:00:00','rlv'
Example 8:
Prints only the LMA sections of the report after 5 seconds:
sp_iqsysmon '00:00:05', 'lma'
Example 9:
Runs the monitor in batch mode for 10 seconds and displays the consolidated statistics at the end of the time period:
sp_iqsysmon '00:00:10', 'mbufpool memory'
Example 1:
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
Example 2:
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 3:
Prints only the RLV and LMA sections of the report:
sp_iqsysmon start_monitor,'filemode','-section rlv lma' sp_iqsysmon stop_monitor
Example 4:
Starts the monitor in file mode and writes statistics for Main Buffer Pool and Memory Manager to the log file every 5 seconds:
sp_iqsysmon start_monitor, ‘filemode’, '-interval 5 -section mbufpool memory' sp_iqsysmon stop_monitor