Mixed Workload Design Priority Guidelines White Paper

Hewlett-Packard Company—528875-001
Priority Guidelines
Prior to determining guidelines for setting priorities of SQL queries, it is necessary to measure and establish
response-time requirements for high-priority transactions. It is then possible to fine-tune the priority of
SQL query workloads to determine the best priority.
If response time requirements cannot be measured or established, then the only recourse is to run the SQL
query at a very low priority to ensure there is no impact to higher priority workloads. While the SQL query
may run significantly slower in the face of higher priority workloads, it may be possible to disable parallel
execution for the SQL query (CONTROL QUERY PARALLEL EXECUTION OFF) and run the SQL
query at a higher priority in some cases.
There are new MEASURE counters available to provide some indication of the amount of query deferral
for each DP2 volume. The DEFREQ-QTIME counter is the amount of time a request is subject to deferral,
and the DEFERRED-QTIME counter is the amount of time a request is deferred.
The ratio between these two counters should increase as the priority of the SQL query is increased.
Because SQL query workloads can vary significantly in resource consumption, and because high priority
workloads and transaction response time requirements can vary, it is difficult to provide general guidelines.
The performance data published with each major release includes data for mixed workload tests. These
include SQL query workloads run at various priority schemes concurrent with higher priority ORDER-
ENTRY benchmarks. While these numbers can be used as a guide, actual results will typically be different
due to the difference with ad-hoc query workloads and the variability of high priority workloads.
In general, when there are multiple partitions processed by a parallel scan within the same CPU, the more
likely the requirement that the priority of the query should be reduced to avoid impact to high priority
transaction response time.
Starting with a lower priority (5-10) and measuring results may be preferable to starting higher (150) and
reducing, especially during live system application activity.