Active Requests vs. unscheduled Requests

[MCMartin]

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?

[MarkCulp · Accepted solution]

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):

• Active requests is the number of requests that the server is currently processing.
• Unscheduled requests is the number of requests that have been received by the server but are waiting (queued) to be processed.
• Multiprogramming level is the maximum number of concurrent requests that can be processed by the server. Internally this number represented the number of "worker" threads in the server that are used to process requests.

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:

• A single request can utilize more than one worker thread if a parallel execution plan is chosen for the query.
• Version 12 has a new "auto tuning multiprogramming level" feature where by the server will adjust the multiprogramming level to maximize throughput (in terms of number of query completions per interval).

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.

HTH

[MCMartin · Accepted solution]

Thanks, in my case it is then the parallel execution as mostly I see this situation after a longer time of inactivity, which gives chance the first query will choose a parallel plan which might prevent the next queries from getting an idle task.