SQL/MP Installation and Management Guide

Measuring Performance
HP NonStop SQL/MP Installation and Management Guide—523353-004
13-10
SQL/MP Measurement Models
index to reduce the number of records searched before returning records that
satisfy the query.
•
SORTS stores the number of times the external sort process was invoked to return
the data in the desired order. A value in this field indicates that the data is not
being retrieved in the order supported by a key (primary key or index). The amount
of time spent sorting is stored in the ELAPSED-SORT-TIME counter.
Performance might decrease in proportion to the amount of time spent sorting
data. By monitoring the sort time of each statement, you can determine the
statements and the associated indexes that might improve performance. An
external sort is not invoked if the number of records to sort is fewer than 400.
•
TIMEOUTS stores the number of times this statement received a request timeout
because of a possible congested disk volume or network. This number should be
zero. You should examine the cause for any number greater than zero.
•
LOCK-WAITS stores the number of times the statement waited for a lock request.
This number should be zero or be quite small. If the number is large for your
application, you should examine the cause. Depending on the situation, you might
consider a finer locking granularity (for example, row locks instead of generic locks
or table locks) or redesigning the database.
•
ESCALATIONS stores the number of times a record lock was escalated to a file
(table) lock. This number should be zero. If this number is greater than zero, you
should consider using a table lock for the program.
Database Access Costs
Use the FILE entity to measure database access costs for SQL tables and indexes.
The following counters provide information for analyzing disk processing costs for the
database.
These counters provide useful information on SQL database tables. You can use these
counters to determine the cost of queries.
•
RECORDS-USED stores the number of rows returned to the SQL executor on
reads, inserts, writes, updates, or deletes.
•
RECORDS-ACCESSED stores the number of rows read by the disk process or file
system to return the RECORDS-USED value. RECORDS-ACCESSED should
always be the same or greater than RECORDS-USED. The ratio between
RECORDS-USED/RECORDS-ACCESSED is the selectivity of the statement.
A query is most efficient when the number of records used is the same or slightly
lower than the number of records accessed. If the number of records accessed is
much larger than the number used, the query is accessing many unnecessary
rows. You can create an index to improve the selectivity.
•
DISK-READS stores the number of physical disk reads performed on the file.