If the active and unscheduled requests are displayed in the performance monitor, I recognized, that even if the number of active requests is rather low the unscheduled requests can have a significant number. E.g. active have a max of 9 and unscheduled have a max of 8. I expected that with default 20 simultaneous tasks, that the above numbers shouldn't occur. Any explanation for this effect? Will increasing the multiprogramming level really help in such a situation?
asked 20 Oct '11, 08:41
You did not mention which version you are using so I am going to assume you are using v12.
First (for those not familiar with these terms):
Typically the server will dequeue requests from the unscheduled request queue and process them up to the point that all available workers are active (i.e. up to the multiprogramming level). There are some situations that will cause a fewer number of active requests:
Therefore you will see unscheduled requests greater than zero and number of active requests less than the current multiprogramming level when (a) one or more of the current active requests is executing a parallel execution plan (and has succeeded in acquiring additional workers to execute the plan) and/or (b) the server has determined that the the current work load on the server can be processed more efficiently by reducing the number of concurrent active requests.
Regarding that last point, note that it is not always better to increase the number of concurrent active requests (multiprogramming level) - sometimes increasing this number will actually decrease throughput due to contention on resources.
answered 20 Oct '11, 08:58