Individual SQL queries run well, but performance decreases when running multiple queries

There are a number of steps that can be taken to improve the performance of SQL queries that run slowly as a group. A query might perform well when run by itself in a test environment, but then run slowly in a production environment with other queries running at the same time.

Symptoms

An SQL query runs reasonably well on its own, but then slows down in a production system when other queries are running at the same time. This may occur every day at a specific time, or seemingly at random.

Looking at the SQL statement there are no apparent reasons for the intermittent performance problem.

Causes

Often these types of performance issues are a result of sort heap memory allocation problems.
Sort heap memory is used by a range of queries (the list is included in the sortheap documentation), so it is important to consider the settings of the following configuration parameters:
  • Sort heap size (sortheap) which specifies the amount of memory allocated for each individual sort
  • Sort heap threshold (sheapthres) which specifies the total amount of memory that is available for all the sorts running across the instance
As the number of queries running at the same time increases, the system might reach the sheapthres threshold, resulting in performance issues.

Resolving the problem

In many cases, the Db2® self-tuning memory manager (STMM) can adjust the sort memory allocation to keep queries running efficiently. See the self_tuning_mem database configuration parameter documentation for more details. If the system is not running STMM then consider the following options:
Modifying SQL
It may be possible to reduce the amount of sort heap memory used by modifying the SQL:
  1. Are all the appropriate indexes created for the query? If a column is known to contain unique values, for example, creating a unique index may reduce the need for sort heap memory.
  2. Determine whether one or more of the SQL statements can be modified to use less sort memory. Is each of the ORDER BY clauses required in the query? Can GROUP BY be used instead of DISTINCT in a subquery?
  3. Make use of column group statistics or statistical views, which in some cases enable the Db2 query optimizer to determine an access plan that uses less sort heap memory.
Increasing sheapthres size
If none of the previous changes to the SQL are possible, or do not improve performance, then increasing the value of the sheapthres parameter is the next consideration. This parameter defines the total amount of memory used by all the private sorts running at any time. When the total private sort memory consumption for an instance reaches this limit, the memory allocated for additional incoming private sort requests is considerably reduced. The post_threshold_sorts monitor element is used to track sort requests that must run with reduced memory once the sheapthres threshold has been exceeded.

See the sheapthres documentation for details on how to increase the amount of memory allocated for all sorts, and how to balance SQL sort performance with memory usage. You will need to consider the amount of memory set aside for any one sort (sortheap) and the typical number of sorts that might occur on the system when performance begins to drop. To verify the effectiveness of increasing the value of sheapthres you should see a decrease in the number of post_threshold_sorts after the change.

Decreasing sortheap size
Finally, in some situations where you are unable to increase the overall sort heap memory (sheapthres) and you have many small sorts, it may be beneficial to decrease the individual sort heap size sortheap parameter instead. This results in less memory allocated for each individual sort, but an increase in the number of sorts that can run at any one time.

You should only consider this option, however, if you can verify that you have many small sorts, each small enough to run within a smaller sort heap. If you set sortheap too small then the sorts will start spilling to disk, which slows down performance because disk I/O is much slower than in-memory operations. You can use the NumSpills value returned as part of the -sort parameter when running the db2pd command to determine whether the individual sort heap size sortheap is set too small for the queries.