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

Enhancing SQL/MX Database Performance
HP NonStop SQL/MX Installation and Management Guide—544536-007
15-16
Optimizing Index Use
SQL/MX processes can consume large amounts of addressable memory space as a
result of:
•
Parallelism among a large number of ESPs
•
The execution of plans that use sort and grouping operators
•
The optimization of complex plans
The heavy consumption of addressable memory space by SQL/MX processes can
lead to insufficient swap file space. As a result, you should provide more
kernel-managed swap space on each CPU by increasing the size of existing swap files
or adding new swap files.
You should periodically monitor your kernel-managed swap files while SQL/MX
programs are being compiled and executed to ensure that adequate swap file space is
available. If the required amount of swap file space is not available, an SQL/MX
compilation might fail, or an executing statement might return an “insufficient memory”
error.
See the discussion on managing kernel-managed swap files in the Kernel-Managed
Swap Facility (KMSF) Manual for information on how to use NSKCOM to monitor
kernel-managed swap file use, how to monitor swap file usage by process, and how to
change your KMSF configuration.
To identify and avoid possible memory contention between either the application or the
master executor and other system components in process space such as QIO, see the
discussion on configuring the QIO subsystem in the QIO Configuration and
Management Manual.
Optimizing Index Use
An index on a table provides an alternate access path that differs from the inherent
access path (clustering key). Indexes improve application performance for data
retrieval operations. When compiling a statement, the SQL/MX optimizer selects the
execution plan for a statement by choosing the best access path to the data. If an
index exists, the SQL/MX optimizer evaluates using the index.
Indexes can also improve performance by eliminating the need for the DP2 process to
access the underlying table. If the query can be satisfied by the columns contained in
the index, the underlying table will probably not be accessed.
When evaluating whether to use an index or a table scan, NonStop SQL/MX compares
the number of base table scan I/Os and the I/Os for index access. The use of
sequential cache for a scan increases the performance of the scan and increases the
likelihood of its use.
Index-only access is faster than a table scan. A sort prevented by index access must
be looked at closely, however, because the cost of a scan plus a sort might be less
than the cost of index and base table access. For more information about selectivity
and cost, see the SQL/MX Query Guide.