Performance Monitor Window
Performance Monitor Window
View Performance Monitor Window
The Performance Monitor window displays charts and information about the status of Adaptive Server and the performance of your database. It has two panes. The left pane is for selecting the database statistic that you would like to view. The right pane displays the charts and information about the selected statistic.
Left pane
The top-level headings for selecting the information and statistics to review are Database Statistics Summary, Monitor Statistics, and Information.
The charts and information displayed in the right pane are dependent upon what is selected in the left pane:
Right pane for Database Statistics Summary
Right pane for Monitor Statistics
The status information for the Performance Monitor is display on the status bar at the bottom of the window.
Right Pane for Database Statistics Summary
The Database Statistics Summary in the Performance Monitor provides an overview of database performance. Important information is displayed on this screen and provides a high level view of the overall performance on CPU utilization for Top N Engines, Device I/O, Network I/O, Data Cache status, and Lock Waits information. Bottlenecks of those statistics can be spotted and you can drill down to and individual process, device, or SQL statement. You can drill down to specific items on the chart by double-clicking on a data point.
Right Pane for Monitoring Statistics
Right Pane for Monitor Statistics
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.
ASE Overall State Statistics
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.
ASE Engines Statistics
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
| Statistic | Definition |
|
CPU (%) |
|
|
SystemCPUTime / CPUTimeEngine * 100% | |
|
UserCPUTime / CPUTimeEngine * 100% | |
|
IdleCPUTime / CPUTimeEngine * 100% |
Variables Definition
|
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. |
Open Database Statistics
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.
Open Objects
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
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.
I/O Statistics
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.
Device 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.
I/O Queue
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 I/O
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.
Process Statistics
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.