How does max_query_tasks help performance?

[Former Member]

I have a client who reports that in general, the response time of slow transactions is better when they turn max_query_tasks off, which is the opposite of what we expected to see.
- What particular types of queries benefit from allowing max_query_tasks (and this is apparently not one of them)? - Even if there is no benefit for this particular query, why would allowing intra-query-parallelism make it run more slowly?

They're using SA 11.0.1.2584

[MCMartin]

Have you compared the query plans for both options? I would seek the cause for your observation there.

[VolkerBarth]

I assume "they turn max_query_tasks off" means they set it to 1, right?

From the docs: "Setting the max_query_tasks option to 1 disables intra-query parallelism."

[Former Member]

Is the setting of "max-query-tasks" the default of 0? (ie unlimited?) And I assume the setting of optimization-goal is 'all-rows'? and the setting of optimization-level is 9?

There are a number of factors that interplay here that can impact actual performance. Without getting into gory detail, the optimizer makes a cost-based decision between parallel and non-parallel plans. Two important things occur when the optimizer considers parallel plans:

1. There are, simply, more plans in the space for the optimizer to choose. Consequently, the optimizer may fail to discover better (possibly simpler) plans in the time allotted for it to do so (controlled by the optimization-level option).
2. The optimizer chooses the degree of parallelism at optimization time; in a server with variable workloads, the number of available workers when the query is executed may not match the desired number. In SQL Anywhere, that's not a really significant problem because even if the number of workers is less than that assumed, the workers that ARE available will get used, and the query won't be starved for resources. On the other hand, if the optimizer made a cost-based decision based on all 4 cores being idle, and it turns out that when executing only one core was available, then the choice of a parallel plan in the first place can be moot.

While our default for max-query-tasks is 0 (unlimited parallelization based on the optimizer's choices) I would be tempted as an administrator to tradeoff higher degrees of parallelism with ensuring that cores/workers are available to handle new connections or other long-running ad-hoc requests that can affect the system's response to peak workloads.

[VolkerBarth]

Glenn, I have a question to the situation you point to in number 2:

With v12's automatic tuning of the MPL feature, would a plan that is chosen for more workers than are available when the execution is about to start

1. increase the number of workers immediately or
2. after the query is processed or
3. not at all?

(Assumed the increased number would still be below the maximum level)?

[Former Member]

The correct answer is

1. If the query is sufficiently long-running, the self-tuning MPL algorithm may decide to add additional workers to the worker pool, which may then be available to run idle sub-plans of a parallel strategy.

[VolkerBarth]

Thanks for the clarification - and, yes, I should've known that smart heuristics don't "increase immediately" but "may decide ... under certain conditions":)