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

HP NonStop SQL/MX Installation and Management Guide—544536-007
15-1
15
Enhancing SQL/MX Database
Performance
To achieve maximum performance, you must provide sufficient hardware to handle the
throughput and size of the application database.
In addition to hardware, many factors affect the performance of a database and
application. Some factors are system dependent, others are application dependent.
The factors discussed in this section are specific performance issues that can arise in
an installed and operating SQL/MX environment.
This section addresses these strategies for enhancing SQL/MX database performance:
•
Using Queries in an SQL/MX Database on page 15-2
•
Understanding the Implications of Concurrency on page 15-2
•
Keeping Statistics Current on page 15-7
•
Creating More Accurate Histogram Tables With UPDATE STATISTICS on
page 15-8
•
Managing SQL/MX Buffer Space on page 15-8
•
Managing DP2 Data Cache Memory Size on page 15-14
•
Maximizing Disk Process Prefetch Capabilities on page 15-15
•
Optimizing SQL/MX Memory Management on page 15-15
•
Optimizing Index Use on page 15-16
•
Performing FUP RELOADs to Generate More Accurate Query Plans on
page 15-17
•
Checking Data Integrity on page 15-18
•
Creating Logical Views of Data on page 15-19
•
Adding and Dropping Partitions on page 15-19
•
Avoiding Automatic Recompilations on page 15-20
•
Matching Block Split Operation to Table Usage on page 15-20