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

SQL Inspector Overview

The SQL Inspector identifies SQL statements with performance problems by extracting SQL statements and SQL performance statistics from the QP Metrics (Adaptive Server 15 or later). The SQL Inspector graphically displays and compares SQL activity statistics to diagnose performance bottlenecks. With the SQL Inspector you can identify the top N most resource intensive SQL statements.

With the SQL Inspector, you can schedule the date and time and the frequency that the SQL statistics are collected in order to identify the impact of SQL activities on database performance. By displaying the statistics on a graph you can see which SQL statements are consuming the most database resources, hence identifying problematic SQL statements quickly and efficiently.

The SQL Inspector window provides a work area for you to add and delete Inspectors, storing information on the Inspector name and description, number of SQL captured, start time, end time, and duration. It offers a way to view the statistics from the SQL statements captured in charts and a grid format. It provides a view of the top N worst performing SQL statements. SQL statements can be sorted by different AND/OR conditions of SQL performance statistics such as number of executions, CPU time, logical reads, physical reads, and others.

SQL statements collected in Inspector files can be also be analyzed with the SQL Scanner module. Once you have identified potentially problematic SQL statements you can send the SQL from the SQL Inspector to the SQL Optimizer, Index Advisor, the SQL Worksheet, or save them to the SQL Repository.

Note: This module is available for Adaptive Server 15 and later and requires that the Adaptive Server monitoring tables are installed.

Or, if you have Adaptive Server 15.0 or later, you can retrieve the SQL statements and performance statistics using QP Metrics if you have access to the sysquerymetrics view.

 

Related Topics

SQL Inspector Privileges

To use the SQL Inspector module you must have access to the sysquerymetrics view in Adaptive Server 15.0 or later.

You must also set up specific configuration parameters in Adaptive Server.

 

Related Topic

Performance Statistics - Monitoring Tables

Each SQL statement captured from the monitoring tables has the following performance statistics:

Statistics

Description

CPU Time

Number of milliseconds of CPU used by the statement.

CPU Time Per Execution

Ratio between the number of milliseconds of CPU used by the statement and the number of executions.

Executions

Number of times the statement was executed during the monitoring period.

Logical Reads

Number of buffers read from cache.

Logical Reads Per Execution

Ratio between the number of buffers read from cache and the number of executions.

Mem Usage (KB)

Number of kilobytes of memory used for the execution of the statement.

Mem Usage (KB) Per Execution

Ratio between the number of kilobytes of memory used for the execution of the statement and the number of executions

Packets Received

Number of network packets received by Adaptive Server.

Packets Received Per Execution

Ratio between the number of network packets received by Adaptive Server and the number of executions.

Packets Sent

Number of network packets sent by Adaptive Server.

Packets Sent Per Execution

Ratio between the number of network packets sent by Adaptive Server and the number of executions.

Pages Modified

Number of pages modified by the statement.

Pages Modified Per Execution

Ratio between the number of pages modified by the statement and the number of executions.

Physical Reads

Number of buffers read from disk.

Physical Reads Per Execution

Ratio between the number of buffers read from disk and the number of executions.

Wait Time

Number of milliseconds the task has waited during execution of the statement.

Wait Time Per Execution

Ratio between the number of milliseconds the task has waited during execution of the statement and the number of executions.

 

Related Topics

Performance Statistics - QP Metrics

Each SQL statement captured from the QP Metrics has the following performance statistics:

Statistics Description

Execution time

How long it takes to execute the SQL statement on the CPU displayed in milliseconds.

Elapsed time

This is a total of the Execution time and the time it takes to parse, compile, and optimize the SQL statement displayed in milliseconds.

Logical IO

Total logical IO reads during the execution of the SQL statement.

Physical IO

Total physical IO reads during the execution of the SQL statement.

Execution count

Number of times the SQL statement has been executed.

Abort count

Number of times the resource governor terminated the SQL statement because a resource limit was surpassed.

The execution time, elapsed time, logical IO, and physical IO statistics each of have three values taken from the number of times the SQL statements was executed: the minimum, the maximum, and the average.

 

Related Topics

Related Documents