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.
They're using SA 126.96.36.19984
asked 24 May '11, 15:12
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.
answered 26 May '11, 12:34