If you have a small database with each table residing on
only one disk and using only one CPU virtual processor, you can take
specific measurements to help you anticipate and correct performance
problems.
To achieve acceptable initial performance on a small
database:
- Generate statistics of your tables and indexes to provide
information to the query optimizer to enable it to choose query plans
with the lowest estimated cost.
These statistics are
a minimum starting point to obtain good performance for individual
queries. For guidelines, see Update statistics when they are not generated automatically.
To see the query plan that the optimizer chooses for each query, see Display the query plan.
- If you want a query to run in parallel with other queries,
you must turn on the Parallel Database Query (PDQ) feature.
Without
table fragmentation across multiple disks, parallel scans do not occur.
With only one CPU virtual processor, parallel joins or parallel sorts
do not occur. However, PDQ priority can obtain more memory to perform
the sort. For more information, see Parallel database query (PDQ).
- If you want to mix online transaction processing (OLTP)
and decision-support system (DSS) query applications, you can control
the amount of resources a long-running query can obtain so that your
OLTP transactions are not affected.
For information
about how to control PDQ resources, see The allocation of resources for parallel database queries.
- Monitor sessions and drill down into various details to
improve the performance of individual queries.
For
information about the various tools and session details to monitor,
see Monitoring memory usage for each session and Monitor sessions and threads.