Chat now with support
Chat mit Support

SQL Optimizer for SAP ASE 3.8 - 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

Performance Monitor Window

Performance Monitor Window

Performance Monitor > Performance Monitor Window > 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

Right pane for Information

The status information for the Performance Monitor is display on the status bar at the bottom of the window.

 

Related Topic

Right Pane for Database Statistics Summary

Performance Monitor > Performance Monitor 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.

 

Related Topic

Right Pane for Monitoring Statistics

Performance Monitor > Performance Monitor Window > 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 + 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

Verwandte Dokumente

The document was helpful.

Bewertung auswählen

I easily found the information I needed.

Bewertung auswählen