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

Enhancing SQLMX Database Performance
HP NonStop SQL/MX Installation and Management Guide—523723-004
16-8
Using SQLMXBUFFER to Improve Database
Performance
•
Changes to UPDATE STATISTICS that affect cardinality estimates. As a result, the
Release 2.1 or Release 2.0 optimizer traverses different plans in the search space
than does the Release 1.8 optimizer.
•
More aggressive tuning of undesirable plans by the optimizer. As a result, the
current optimizer does not consider some Release 1.8 query plans.
For more information about the CONTROL QUERY SHAPE statement and the
UPDATE STATISTICS command, see the SQL/MX Reference Manual.
Using SQLMXBUFFER to Improve Database
Performance
SQL/MX Session Data Space and Data Cache Guidelines
For data volumes that contain tables that are the subject of OLTP, have high
transaction volumes, and have a very large number of opens, increasing the amount of
memory available for SQL/MX session data can improve performance. If the amount of
messages and message bytes per transaction incurs a spike during peak periods,
increasing the SQL/MX session space can reduce the per transaction message and
message byte counts.
Any increase in the size of SQL/MX session data space reduces the maximum size of
data cache by a corresponding amount. The default size of SQL/MX session data
space is 128 MB, and the maximum size of data cache with this default is about
768 MB. For example, if the SQL/MX session data space is increased from 128 MB to
512 MB, the maximum size of data cache is reduced from about 768 MB to a little less
than 400 MB.
Both data cache and SQL/MX session data cache must be configured with adequate
physical memory in both the primary and backup processors for the data volume.
While both are contained within virtual space, and clean pages can be stolen by the
memory manager, over-configuration can lead to excessive page swapping and
performance loss. In the worst-case scenario, an over-configured data cache can
result in memory pressure, which can cause a variety of failures.
If the data volume contains tables that are the subject of DSS, with low transaction
volumes or a relatively few opens, decreasing the amount of memory available for
SQL/MX session data can enable a corresponding increase in the size of cache, which
can improve the overall performance of the DSS application.
Note. The default and maximum data sizes listed are subject to change. The amount of total
memory available to data cache and SQLMXBUFFER space might even be reduced. And DP2
automatically limits the amount actually provided to the space based on the availability of
virtual space. As a result, the amount of space you request might not always be granted.