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

Martin's gravatar image

Martin
8.6k116151237
accept rate: 14%


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

permanent link

answered 20 Oct '11, 08:58

Mark%20Culp's gravatar image

Mark Culp
23.0k9130270
accept rate: 40%

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.

(20 Oct '11, 09:47) Martin
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×242

question asked: 20 Oct '11, 08:41

question was seen: 691 times

last updated: 20 Oct '11, 09:47