SQL Optimizer for SAP ASE 3.9 - User Guide

Right Pane for Monitoring 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

Variables Definition

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.

Related Topic

Performance Monitor Window

Right Pane for Information

The Information section in the Performance Monitor provides information about Cache, Processes, SQL Statements, Locks, Sys Worker Threads, Sys Waits and the Error Log.

Cache Information

The Cache Information section has the following information:

Process Information

The Process Information section has the following information:

SQL Statement Information

This SQL Statement Information section provides information about currently executing SQL statements and recently executed SQL statement.

Locks Information

Returns a list of all locks that are being held, and those that have been requested, by any process, for every object.

Sys Worker Threads

Provides server-wide statistics related to worker threads.

Sys Waits

Provides a server-wide view of where processes are waiting for an event.

Error Log

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.

Related Topic

Performance Monitor Window

Status Bar

The status bar at the bottom of the Performance Monitor window has the following information.

Related Topic

Performance Monitor Window

Open the Performance Monitor

To open the Performance Monitor window

Click .

Related Topic

Performance Monitor Window