A question that has come up as part of that other one, related to the tuning of the "multiprogramming level" (MPL) in SA 12 and above and the usage of external environments: Tasks typically service user requests. So setting the maximum MPL to 80 would mean the database server can handle up to 80 active requests concurrently - if there are more requests those will be queued. To cite from the v16 docs on Threading behavior:
What other kind of tasks may use workers from the worker thread pool?
asked 01 Jun '15, 04:12 Volker Barth |
FWIW I do remember this being covered some place before, in a response? ... or a whitepaper? If I find that I will link it in later. The various -gn* switches controls the number of workers and your question is actually about workers. In contrast sa_conn_info() shows information only about connections (connection contexts for both the external and internal contexts). There is not a 1-to-1 mapping between those concepts. Tasks is what is actually mapped onto workers and it is a 1 to optionally-many relationship at both levels here. Connections (internal or external) are optionally mapped onto 1 or more Tasks (when there is an active query/operation) and Tasks (when scheduled) are mapped to Workers. Needless to say unscheduled tasks do not currently consume a worker. If a task needs a connection-context there will be a trackable connection for that (I don' know of any exceptions to that rule). Of course it is workers that are eventually scheduled on actual system threads (in the typical SMP and OS-specific way) running on some core. As to what consumes the extra workers you are worried about? When you use external functions/procedures of any kind (Not the old school DLL calls but the newer external environments of any sort) or remote server accesses (remote procedure call or accessing proxy tables) those do involve extra workers (typically 1 per external reference so there could be multiple per query). These execute on the same connection context as the triggering internal/external connection so they do not show up as extra internal connections. For remote server connections those extra connections will show up on the remote server but not the caller system. For the per-database external environments (Java or CLR) there will be an extra connection for the call back mechanism used for that; I'd have to check on per-connection external environments. All of this is in addition to the workers used for parallelism which do show up as extra internal connection contexts in addition to the extra workers involved (not all of which may be consuming an actual worker if not scheduled). Not all connections consume a worker at any moment in time. Connections without requests (and possibly internal parallel worker connections that don't execute a parallel sub-plan due to runtime adjustments) do not tie up a worker. But connections can often be associated with multiple workers when doing something complex using multiple features. Events, cleaner, stats cleaning, ... have their on internal connections (as you've already noted) and tasks and workers when running ... Of course I haven't yet covered HTTP requests (yep 1 connection and 1 worker) or remote HTTP requests (yep an additional 1 worker on the connection context) and of course HTTP requests operate over queries, functions, procedures, . . . and there can be a fan-out there ... again. Did I miss anything else? AFAIK The basic link for the this "threading model" is in every DCX edition. I don't find any official break down on the various kinds of tasks nor a counter for that. People do track the current level of utilization of workers by monitoring the Unscheduled Requests and Active Requests but those are best used for performance impact/efficiency measurements; not so helpful for breaking out or enumerating the current break down on what those tasks are. Combining those with the sa_conn_activity()/info() information gets you a little closer. answered 01 Jun '15, 11:45 Nick Elson S... Big thanks for the elaborate answer, Nick:) (I have certainly overseen the HTTPS requests but my according engine does not run a web server nor web client procedures so those do not have influence in my case.) I'm still puzzled (and that detail seems unanswered) whether tasks for external functions and remote data access will count for the MPL - and based on my observations, they don't seem to be counted for "ActiveReq", right?
(01 Jun '15, 12:14)
Volker Barth
|
I just added the link to active requests before I noticed your latest post but you are correct! According to that DCX article it is only workers assigned to client-side requests contribute to the ActiveReq counter. It is also underscored by this other article too. They do consume workers and that will affect the way things get optimized (if parallelism is your concern), scheduled, or delayed if unscheduled (but maybe not counted if not from a user request). answered 01 Jun '15, 12:32 Nick Elson S... Going back to your question about MPL in the subject. What are you most concerned about? Are you asking if the extra workers get counted/affect the dynamic-MPL algorithm? Or are you just worried about the tracking the count. If it is not yet clear the worker count is the total count and these 'other workers' are drawn from the same worker pool (even though that does not show up clearly in the various diagrams and descriptions). There is no 'other workers' pool for that. That said tracking that is the difficult part when using external environments. Those familiar with the old external function call, that did not take the extra workers the newer external environments do (yep going to correct my original posting on the ext. func. part of that next) can be easily surprised by this ... as the person posting the original question on that other thread was. And, if it matters, I do believe it will also driver the MPL algorithm.
(01 Jun '15, 12:53)
Nick Elson S...
Replies hidden
Comment Text Removed
Comment Text Removed
Comment Text Removed
As stated in my comment on the question, I'm not about to solve a problem (as there is none) but trying to understand the automatic MPL behaviour. What I have observed on my test system (see my comment on the question) has been confirmed in the meantime: The system is basically idle, there are a few almost idle connections - and there are events running regularly, e.g. one running every 30 minutes to backup the log, check for its validity (via the DBTranslate() API) and copy it to a network share via xm_cmdshell. And during the event (which calls several external functions from one DLL loaded in an external C environment), each time the current MPL level is increased from 3-4 to 13-14 (after a thread deadlock is reported) and then shortly after decreased to 3-4. The somewhat funny part is that according to the MPL messages in the console log, the increase does take place when the event is almost finished (and most external calls have been made), so I don't think it will somehow speed up things... Given the fact (if my understanding of your statements is correct) that
it's still hard to find out why the current MPL is increased that much. But if there is no property that does count all workers in use, I'll take that as given:)
(02 Jun '15, 03:31)
Volker Barth
Sorry, I have to correct my observation: The MPL is not increased during the course of the event but decreased (while the copying to the net share takes place, apparently since this is a somewhat "slow", IO-focussed action) and is then increased again to the typical value. - Still it remains a mystery why that strong decrease from 13-14 to 3-4 happens. But I can live with that... Nick, in case there is no property that counts these "external" workers, consider my question as answered:)
(02 Jun '15, 04:00)
Volker Barth
|
As to the background:
On a rather idle 12.0.1.3324 server (primarily used for tests) with automatic MPL with default settings, it seems that the system tends to lower the current MPL to 3-5 (since there are usually only 1-3 active requests), however, as soon an event is triggered and calls an external function (or xp_cmdshell?), the console log displays a thread deadlock and increases the current MPL to 14 or 15 - although property('ActiveReq') never seems to make it beyond 3...
After a while, the current MPL is decreased to 5 and then further to 4 and 3 (that seems quite a pattern) before the next increase takes place. (FWIW: One of these few active requests is a ISQL session with a loop that selects the desired properties and writes them to the console log every 30 seconds.)
I'm aware that a loop that queries the state every 30 seconds will not notice each system state, however, the number of unscheduled requests (i.e. property('UnschReq')) seems always 0 in my small test, which would not imply the need for a higer MPL setting...
That observation makes me think that external functions seem to require more threads than "ActiveReq" will tell, or at least that the automatic MPL adjustment increases its settings based on much more information than the server properties seem to reveal.
Note: That behaviour is not a problem to solve, it's just something I'd like to understand.
Just to add: The pattern to decrease and increase the MPL is at least documented with the property "ThreadDeadlocksAvoided" - its value will increase over time.