The Monitor Statistics in the Performance Monitor displays various charts that correspond to the statistic selected in the left pane. You can drill down and zoom in or out on these charts.
Provides information regarding the overall performance state of Adaptive Server. Information includes number of lock waits which exceeded the lock wait threshold, number of dead locks, and number of connections.
At the bottom of the right pane is the Information Box that has some static information of overall performance state of Adaptive Server. To see this information, move the cursor right above the word "Information" until the cursor changes and you can drag and expand the Information Box.
In a system with multiple CPUs, you can enhance performance by configuring Adaptive Server to run using multiple Adaptive Server engines. Each engine is a single operating system process that yields high performance when you configure one engine per CPU. In the Adaptive Server Engines Statistics, multiple engines can be monitored at the same time. Various CPU statistics are displayed on the latest snapshot and historical line chart format. A number of definitions are introduced by the Performance Monitor that are not documented in the Adaptive Server Performance and Tuning: Monitor and Analyzing manual.
Statistic | Definition |
CPU (%) |
|
SystemCPUTime / CPUTimeEngine * 100% | |
UserCPUTime / CPUTimeEngine * 100% | |
IdleCPUTime / CPUTimeEngine * 100% |
Variable |
Definition |
SystemCPUTime |
Time (in seconds) the engine has executed system database services in last interval. |
UserCPUTime |
Time (in seconds) the engine has executed user commands in last interval. |
CPUTimeEngine |
Total time (in seconds) the engine has run in last interval. |
IdleCPUTime |
Time (in seconds) the engine was in idle spin mode in last interval. |
Provides state and statistical information pertaining to databases that are currently in use. Two dynamic statistical items are monitored for individual databases in the Open Database Statistics section: Append Log Requests and Append Log Waits. These items are displayed on a snapshot bar chart and a historical line chart.
These statistics are not collected unless the checkbox in front of the option is checked. Since the enabling of this monitor option may result in the capture of a large amount of information from the database, it is not recommended to turn it on if you do not need to troubleshoot a current database performance problem.
For open database objects such as tables, indexes, views, and stored procedures, statistical information is consolidated by database, statistics item, and object type.
These statistics are not collected unless the checkbox in front of the option is checked. Since the enabling of this monitor option may result in the capture of a large amount of information from the database, it is not recommended to turn it on if you do not need to troubleshoot a current database performance problem.
Cache Statistics are used to monitor the Data Cache, Procedure Cache, and Cache Pool information. Most of the statistics displayed in this section are directly extracted from the monitoring tables, but the following items are calculations from these retrieved statistics:
Data Cache Hit Ratio for the data cache for the life of Adaptive Server.
Data Cache Hit Ratio(%) = LogicalReads * 100 / CacheSearches
Data Cache Hit Ratio for the data cache in the last interval.
Data Cache Delta Hit Ratio(%) = Delta_LogicalReads * 100 / Delta_CacheSearches
Procedure Cache Hit Ratio for the procedure cache for the life of Adaptive Server.
Procedure Cache Hit Ratio(%) = (Requests - Loads) * 100 / Requests
Procedure Cache Hit Ratio for the procedure cache in last interval
Procedure Cache Delta Hit Ratio(%) = (Delta_Requests - Delta_Loads) * 100 / Requests
Note: The "Delta_" in the above equations denotes that the difference in the statistics values for the last polling interval.
Normally, a Hit Ratio greater than 90% is considered healthy and indicates that no extra memory is necessary. But there are some situations that a large Hit Ratio is only contributed by a small piece of a procedure or from Data Pages resident in memory that have been executed million times during the day. So, carefully review these figures to understand the behavior your applications. Do not rely only on these figures to judge the healthiness of memory usage on your systems.
The I/O Statistic provides a compressive view of the I/O activity on Adaptive Server. It is broken down into Device I/O, I/O Queue and Network I/O.
The Top-N I/O devices are displayed on this monitor section to keep track of individual device I/O trends or the I/O devices which are most active on a real time basis. An intensive I/O device may need further investigation or reallocation of hard disks. Reads for user devices and temporary database reads/writes may result from poorly performing SQL statements.
The I/O Queue statistics are broken down into data and log I/O for normal (User) and temporary databases on each device. I/O statistics include the information of I/O Operations and I/O Time. Too many I/O Operations and I/O Time on temporary databases means that there may be many temporary table operations in your application processes; or your SQL statements are using too many worktable operations or sorting operations. An Index or SQL syntax restructure may help to improve the problem. SQL Optimizer provides the SQL Optimizer and Index Advisor to help identify missing indexes and to optimize SQL.
Network IO statistics for both Received and Sent information are displayed in this monitor section. For a high Packet/Byte Received situation, you should consider converting your dynamic SQL or Transact-SQL into Stored Procedure calls to Adaptive Server. Thousands of dynamic SQL calls within a loop is normally the problem in most applications, it not only increases the network traffic, but also introduces extra workloads to Adaptive Server. If there are high values for Packet/Byte Sent, look to see if the applications are sending unnecessary data to the Client application. For example; in some online queries, the users may need only the first few records to review, a filtering criteria should be added to those applications to narrow the result set being retrieved from the database. Furthermore, some development tools, such as PowerBuilder, provide options to retrieve necessary data only if users want to see more by pressing a next-page button. Stored Procedures can be created in the SQL Worksheet module in SQL Optimizer.
The Process Statistics provides very comprehensive information for all executing processes.
These statistics are not collected unless the checkbox in front of the option is checked. Since the enabling of this monitor option may result in the capture of a large amount of information from the database, it is not recommended to turn it on, if you do not need to troubleshoot a current database performance problem. When you are troubleshooting use Manual Refresh Data to take a snapshot, which provides very good insight on processes running status, such as Top-N CPU intensive processes. Processes that are blocked by other processes are marked with a special icon and the related blocking processes can also be displayed.
The Information section in the Performance Monitor provides information about Cache, Processes, SQL Statements, Locks, Sys Worker Threads, Sys Waits and the Error Log.
The Cache Information section has the following information:
Statistic | Description |
Provides statistics for all objects and indexes with pages currently in a data cache. | |
Provides statistics for all procedures currently stored in the procedure cache. |
The Process Information section has the following information:
Statistic | Description |
Processes |
Provides detailed statistics about processes that are currently executing or waiting. |
Process Objects |
Provides statistical information regarding database objects that have been accessed by processes. |
Process Procedures |
Provides a list of all procedures that are being executed by processes. |
Provides information enabling processes to be tracked to an application, user, client machine, and so on. | |
Provides information about process use of worker threads. | |
Process Waits |
Provides a server-wide view of where processes are waiting for an event. |
This SQL Statement Information section provides information about currently executing SQL statements and recently executed SQL statement.
Statistic | Description |
Executing SQL |
Provides information for currently executing statements. |
Executed SQL |
Provides statistics pertaining to the most recently executed statements. The maximum number of statement statistics returned can be set with statement pipe max messages Adaptive Server configuration parameter. |
Returns a list of all locks that are being held, and those that have been requested, by any process, for every object.
Provides server-wide statistics related to worker threads.
Provides a server-wide view of where processes are waiting for an event.
Returns the most recent error messages from the Adaptive Server error log. The maximum number of messages returned can be set with the errorlog pipe max messages Adaptive Server configuration parameter.
The status bar at the bottom of the Performance Monitor window has the following information.
Item | Description |
Server |
Adaptive Server name |
User |
Default user for the login account |
Login |
Login account name |
Interval |
Interval setting for retrieving the statistics from the monitoring tables from the Preferences settings. |
CNF |
The Chart Data Normalization Factor from the Preferences settings. |
Monitor Status Icon |
When the Monitor Status icon at the far right of the status bar is green , the Performance Monitor is idle. When the Status icon at the far right of the status bar is red , it is retrieving the statistics from the monitoring tables. |
To open the Performance Monitor window
Click .
© 2024 Quest Software Inc. ALL RIGHTS RESERVED. Terms of Use Privacy Cookie Preference Center