Why is max_query_tasks a database option?

[MCMartin]

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.

[VolkerBarth]

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.

[MCMartin]

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.

[VolkerBarth]

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...

[MCMartin]

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.

[Breck_Carter]

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".

[Former Member · Accepted solution]

Since worker threads are a server entity, you are right to question why something like max_query_tasks is a database option rather than a server option.

Our choice in favour of the database option was largely based on two premises:

1. For the majority of customers, particularly where SQL Anywhere is embedded with the application, the server runs a single database.
2. It was desirable to offer a mechanism that could be altered for a particular user or connection, so that some connections could, automatically, utilize additional parallelism but other connections would be restricted from doing so.

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.

[VolkerBarth · Accepted solution]

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...

[Former Member · Accepted solution]

PRIORITY impacts the scheduling of requests to workers, but is somewhat orthogonal to the degree of parallelism.

[VolkerBarth · Accepted solution]

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

1. set max_query_tasks to the MultiProgrammingLevel - 1 (though v12's auto tuning might increase the level automatically...) or

2. leave the default max_query_tasks of 0 but set the priority to Below Normal or a lower level (assuming other connections work with Normal priority).

In my understanding, both would leave at least one worker for other connections. Is this correct?