SQL/MX 3.1 Installation and Management Guide (H06.23+, J06.12+)

Enhancing SQL/MX Database Performance
HP NonStop SQL/MX Release 3.1 Installation and Management Guide—663852-001
15-8
Managing SQL/MX Buffer Space
Managing SQL/MX Buffer Space
This subsection contains this information:

How DP2 Manages and Reuses Query Plan Fragments on page 15-9

Causes and Symptoms of Query Plan Fragment Reuse Failures on page 15-9

Reduction of Plan Fragment Size for Unique Queries on page 15-10

Using SCF STATS DISK to Monitor SQL/MX Statistics and Reuse Failures on
page 15-10

Using SCF ALTER DISK to Resize the SQLMXBUFFER Attribute on page 15-13

Strategies for Reducing Reuse Failures on page 15-14
For every disk volume running NonStop SQL/MX on your system, approximately 1.1
GB of virtual space must be shared between the DP2 data cache, SQL/MX buffer, and
lock tables. The DP2 data cache provides temporary storage for disk blocks containing
table and index data that are referenced frequently. The SQL/MX buffer stores the
query plan fragments that get downloaded into DP2 and working data in transit
between the Executor or an ESP and the local DP2 process or another DP2 process.
An important challenge in managing this limited virtual space is achieving and
maintaining the proper balance between the SQL/MX buffer and DP2 data cache. If
you allow either one to use too much of this space, it can adversely affect the
performance of the other and the system in general.
Any increase in the size of SQL/MX buffer space reduces the maximum size of the
DP2 data cache by a corresponding amount. The default size of the SQL/MX buffer is
128 MB, and the maximum size of the DP2 data cache with this default is about
768 MB. If, for example, you increase the SQL/MX buffer space from 128 MB to
512 MB, the maximum size of the DP2 data cache is reduced from about 768 MB to a
little less than 400 MB.
Whether or not the SQL/MX buffer on a given disk is likely to need larger or smaller
amounts of buffer space depends on the SQL/MX environment of that disk. DP2
processes running a high volume of OLTP transactions with a large number of opens
generally require more SQL/MX buffer space than those running DSS transactions with
relatively few opens.
Both SQL/MX buffer space and DP2 data cache can be taken by the memory manager
when they are not in use. When there is no approaching memory pressure, DP2 issues
long-term locks on both DP2 data cache and SQL/MX buffer pages, thereby preventing
those pages from being taken. When memory pressure occurs (for example, when
there is insufficient physical memory to meet the demand for pages), DP2 releases the
locks so that pages can again be stolen by the memory manager.
Use the SCF ALTER DISK command to set or change the size of the SQL/MX buffer
on a disk volume. Although you can configure the buffer size to a maximum of 768 MB,
you should avoid sizing it above 512 MB to minimize the adverse impact on DP2 data