SQL/MX 2.x Installation and Management Guide (G06.24+, H06.03+)

HP NonStop SQL/MX Installation and Management Guide—523723-004
15-1
15 Measuring Performance
During the life of an SQL application, you might need to measure the performance of
all, or part, of the application. Several NonStop software products can provide
statistical information about performance.
Collecting these statistics requires an in-depth understanding of the system, the layout
of the database tables, and the use of the application programs. You usually gather
statistics when you are:
•
Running a performance benchmark.
You obtain statistics for a benchmark during ideal conditions when all volumes and
nodes are available and functioning at peak performance. You obtain these
statistics on a finite set of data loaded for the best possible performance.
•
Diagnosing a performance problem.
You obtain statistics to determine the cause of a problem. You might have to obtain
several samples during different periods of time to compare the results.
•
Taking a general sampling.
To monitor performance as the SQL database grows and changes, periodically
obtain a sample of statistics and compare the results against previous samples.
•
Making an equipment change or move requiring database relocation.
Whenever the database is moved or changed, you should obtain a performance
sampling. The move or change can affect performance.
This section addresses these topics:
•
SQL/MX Tools for Gathering Statistics on page 15-1
•
Measure Performance Measurement Tool on page 15-4
SQL/MX Tools for Gathering Statistics
Both MXCI and the SQL programmatic interface have tools for gathering statistics. The
MXCI commands that display statistics are:
•
SHOWLABEL command
•
SET STATISTICS ON command
•
DISPLAY STATISTICS command
Note. MXCI queries do not collect Measure statistics, which are collected only for embedded
SQL programs. To obtain performance statistics in MXCI, use DISPLAY STATISTICS, and you
should obtain the same data as from Measure.