SQL/MX Release 2.0 Best Practices

Database Sizing Considerations 25
MAX under 4KB Blocks The amount of 4 KB blocks that can be used within the SQL/MX buffer space
for query fragments.
Attempts under Reuse The number of times an attempt was made to reuse information in the
SQL/MX buffer.
OK under Reuse The number of times an attempt to reuse information in the SQL/MX buffer
was successful.
Failed FST and Failed
ID under Reuse
The number of times an attempt to reuse information in the SQL/MX buffer
was unsuccessful. An unsuccessful attempt could occur because one or
more 4KB blocks of the query fragment space were stolen by another query.
Therefore, the query plan would need to be resent from the application to
the DP2.
If a disk volume contains many different SQL objects (tables and/or indexes), query fragments from each
SQL/MX executing process will be stored in the SQL/MX buffer for the DP2. Therefore, placing many SQL
objects on a volume can potentially fill SQL/MX buffers. Monitoring SQL/MX buffer STATS through SCF is
important. When FST or ID Failures occur, SQL/MX buffer thrashing is taking place. To alleviate SQL/MX
buffer thrashing, avoid placing too many SQL objects on the same volume. Also, avoid placing frequently
accessed SQL objects on the same volume.
Before monitoring SQL/MX buffer statistics for an application, the statistics should be reset. Use SCF DISK
STATS $vol, SQLMX, RESET.
If SQL/MX buffer space is under-configured, these errors could occur:
8570 SQL/MX could not allocate sufficient memory to build query.
8571 SQL/MX could not allocate sufficient memory to execute query.
Increasing SQL/MX buffer space can help to eliminate this error condition.
Setting the SQLMXBUFFER Attribute by Using SCF
For data volumes with SQL/MX, you can change the size of the segment data area by changing the
SQLMXBUFFER attribute. The volume must be in the STOPPED state to allow this attribute to be altered.
From SCF, set SQLMXBUFFER as:
ALTER DISK $volume, SQLMXBUFFER n
(where n is the unit in megabytes)
The minimum SQLMXBUFFER size is 1 MB, and the maximum size is 768 MB. The default value for
SQLMXBUFFER is zero, which causes DP2 to automatically establish the appropriate size for SQL/MX data
space. For volumes accessed by NonStop SQL/MX, use the default value (0) or values of at least 128 MB.
Increasing SQLMXBUFFER beyond 128 MB effectively reduces the maximum space available for cache.
You do not need to alter SQLMXBUFFER unless more virtual memory is needed for cache.
HP recommends that the SQLMXBUFFER size be divisible by 16.
For detailed information about the SQLMXBUFFER attribute, see the SCF Reference Manual for the
Storage Subsystem.
Optimizing SQL/MX Memory Management
Certain embedded SQL/MX programs can use very large portions of the process’ flat segment address
range for:
• Application-addressable memory
• Memory space for the master executor to run SQL/MX statements
The memory space requirement for the master executor can be minimized by using parallelism to
distribute the work to other processes, as explained in the discussion of parallelism in the SQL/MX Query
Guide.
SQL/MX processes can consume large amounts of addressable memory space as a result of: