|
Why is max_query_tasks a database option? I would have expected it to be a database server option, as allowing one database to use all available tasks can have side effects on other databases. So if I want to limit the number of parallel tasks which can be consumed by one query I would expect to do this for the whole engine whithout having to configure all databases which are run in one server instance individually. |
|
Since worker threads are a server entity, you are right to question why something like Our choice in favour of the database option was largely based on two premises:
The solution for item (2) is the database option mechanism. Item (1) makes the use of the option mechanism more palatable. Moreover, with the automatic multiprogramming level self-tuning now available with Version 12, the database option provides restrictive control over the use of parallelism, while the server self-manages the number of worker threads to maximize the server's throughput across the board. 1
BTW, what role does the priority option of a connection play here? It seems to be a further means to influence the work of one particular connection (or one particular database when using the max_priority option) w.r.t. other connections...
(29 Oct '11, 17:34)
Volker Barth
Replies hidden
PRIORITY impacts the scheduling of requests to workers, but is somewhat orthogonal to the degree of parallelism.
(31 Oct '11, 09:12)
Glenn Paulley
...so if I would like to use parallelism for a particular connection but would like to avoid to let it use all workers I could set that connections properties/options as following:
In my understanding, both would leave at least one worker for other connections. Is this correct?
(31 Oct '11, 18:57)
Volker Barth
|
I can't answer that, of course:)
However, AFAIK there seems to be no way to dedicate database server resources to particular databases - i.e. when one database has many users and the other has less, it would be "normal" that the server would dedicate more of its resources to the first database.
As this seems a general rule, the mentioned database option simply can influence other databases as much (or as less) as all other activities that ask for more requests...
Just to add: There's more control how much resources can be bound to one single connection, e.g. via max_cursor_count, max_priority and the like - but again these are database options.
Based on this database option I can restrict one database from consuming all resources, anyway if I want to have a general rule for the database server driving multiple databases I have to set this option in each of them.
How would you do that?
AFAIK, setting max_query_tasks to 1 will only prevent intra-query-parallelism, however a database that has many active (non-parallel) requests might still gain access to most or all of the workers when the other databases are not busy...
In general you are right, anyway my point is, that through query parallelism it is possible, that one query blocks all available workers just because they are available, which in the end will block any other query from being executed immediately, therefore I am considering if a restriction e.g. to 4 parallel tasks might be beneficial. In this case a new query would at least get a resource at all.
FWIW if you look at the runtime properties you will see this is a connection-level property which may be initialized for new connections via the database option SET OPTION PUBLIC or set dynamically at the connection level via SET TEMPORARY OPTION. That functionality should probably remain the way it is, but perhaps a upper-bound (a "cap") should be set via sa_server_options() which is AFAIK the only way to set "server-level options".