on 2011 May 24 4:12 PM
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
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:
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
(Assumed the increased number would still be below the maximum level)?
The correct answer is
User | Count |
---|---|
69 | |
10 | |
9 | |
6 | |
6 | |
6 | |
6 | |
5 | |
5 | |
5 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.