SQL/MP Installation and Management Guide

HP NonStop SQL/MP Installation and Management Guide—523353-004
13-1
13 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 under one of these conditions:
•
A benchmark of performance. Typically, 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.
•
A performance problem. Typically, 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.
•
A general sampling. To monitor performance as the SQL database grows and
changes, you should periodically obtain a sample of statistics and compare the
results against previous samples.
•
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 provides an overview of the tools you can use to gather statistics. You can
then use these statistics to determine ways of enhancing performance for your
application. Enhancing performance is discussed in Section 14, Enhancing
Performance.
SQL/MP Tools for Gathering Statistics
Both SQLCI and the SQL programmatic interface have tools for gathering statistics.
The SQLCI commands that display statistics are:
•
FILEINFO utility
•
SET SESSION STATISTICS ON command
•
DISPLAY STATISTICS command
The programmatic data area that receives statistical information is the SQL statistics
area (SQLSA).