Chat now with support
Chat with Support

SQL Optimizer for SAP ASE 3.9.1 - User Guide

Introduction Tutorials Preferences Editor Functions SQL Information and Functions Performance Monitor SQL Inspector SQL Collector for Monitor Server SQL Scanner Index Advisor SQL Optimizer
SQL Optimizer Overview Optimization Engine Common Coding Errors in SQL Statements What Function Should l Use to Retrieve the Run Time? Unsatisfactory Performance Results SQL Optimizer Functions SQL Editor Optimized SQL Activity Log
SQL Worksheet SQL Formatter Database Explorer Code Finder Object Extractor SQL Repository Index Impact Analyzer Index Usage Analyzer Configuration Analyzer Migration Analyzer Abstract Plan Manager User-Defined Temp Tables SQL History Legal Information

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

Statistic Definition

CPU (%)

(SystemCPUTime + UserCPUTime) / CPUTimeEngine * 100%

System CPU Time (%)

SystemCPUTime / CPUTimeEngine * 100%

User CPU Time (%)

UserCPUTime / CPUTimeEngine * 100%

Idle CPU Time (%)

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.

 

Related Topic

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:

Statistic Description

Cached Objects

Provides statistics for all objects and indexes with pages currently in a data cache.

Cached Procedures

Provides statistics for all procedures currently stored in the procedure cache.

Process Information

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.

Process Lookup

Provides information enabling processes to be tracked to an application, user, client machine, and so on.

Process Worker Thread

Provides information about process use of worker threads.

Process Waits

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

SQL Statement Information

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.

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

Status Bar

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 image\I_GreenPMStatus.gif, the Performance Monitor is idle. When the Status icon at the far right of the status bar is red image\I_RedPMStatus.gif, it is retrieving the statistics from the monitoring tables.

 

Related Topic

Open the Performance Monitor

To open the Performance Monitor window

Click image\B_PerformanceMonitor.gif.

 

Related Topic

Related Documents