Database server configuration of the multiprogramming level

The database server's multiprogramming level is the maximum number of tasks that can be active at a time. When a client-side request arrives at the database server, the
task created for that request is assigned to a worker, if one is available. A request with a worker assigned to it is called
an active request. If all available workers are busy, then the request is placed in a special queue called the unscheduled request queue and
the request is classified as an Unscheduled Request. Similarly, an active task is one that is currently being serviced by
a worker. An active task may be executing an access plan operator, or performing some other function, but may also be blocked,
waiting for a resource (such as an I/O operation, or a lock on a row). An unscheduled task is one that is ready to execute,
but is waiting for an available worker. The number of active tasks that can execute simultaneously depends on the number of
server threads and the number of logical processors in use on the computer.

SQL Anywhere lets DBAs choose between allowing the database server to dynamically tune the multiprogramming level based on
server throughput (the default) or configuring the multiprogramming level manually. You can configure the multiprogramming
level settings when you start a database server by specifying network database server options (-gna, -gnl, and -gnh), or after
the database server is running by using the MinMultiprogrammingLevel, MaxMultiprogrammingLevel, and CurrentMultiprogrammingLevel
properties with the sa_server_option system procedure.

The following table summarizes the command line and server options that control the database server's multiprogramming level:

Database server option (starting database servers)

sa_server_option value (running database servers)

Description

-gn

CurrentMultiProgrammingLevel

Sets the multiprogramming level of the database server.

-gna

AutoMultiProgrammingLevel

Turns on and off dynamic tuning of the network database server's multiprogramming level

-gnh

MaxMultiprogrammingLevel

Sets the maximum number of tasks that the network database server can execute concurrently

-gnl

MinMultiProgrammingLevel

Sets the minimum number of tasks that the network database server can execute concurrently

SQL Anywhere network servers support both dynamic and manual tuning of the server's multiprogramming level.

Dynamic tuning of the multiprogramming level
SQL Anywhere network servers can automatically monitor the throughput level of the database server and determine how
the multiprogramming level should be adjusted in response to the current workload. The network database server uses a hill-climbing
algorithm, as well as a parabola approximation approach, to decide on the adjustment that needs to be made. If an increase
in the multiprogramming level results in an increase in the network database server throughput level, then the network database
server proceeds with the increase. If the increase in the multiprogramming level results in degradation in throughput, then
the network database server lowers the multiprogramming level. The network database server continuously monitors the throughput
level and changes the multiprogramming level to improve server throughput. For workloads that consist of short bursts of a
large number of requests followed by long idle periods, it is best to set the minimum multiprogramming level to the maximum
expected concurrency level. This configuration ensures that the network database server is responsive during the short bursts
of requests.

Dynamic tuning of the multiprogramming level is enabled by default (-gna 1). The -gnl and -gnh network database server options
set the minimum and maximum values for the multiprogramming level that the dynamic tuning algorithm uses. When dynamic tuning
is turned on, the network database server also attempts to eliminate thread deadlock issues by automatically increasing the
multiprogramming level each time a thread deadlock condition arises. The network database server continues increasing the
multiprogramming level up to the allowable MaxMultiprogrammingLevel value. Once the MaxMultiprogrammingLevel value is reached,
the network database server starts returning thread deadlock issues to client applications.

Manual tuning of the multiprogramming level
You must turn off dynamic tuning of the multiprogramming level before you can adjust it manually. It is recommended
that you test your application's workload to analyze the effects of the database server's multiprogramming level on server
throughput and request response times. You can use the Windows Performance Monitor or the Performance Statistics utility (dbstats)
on Unix to help you analyze database server behavior when testing your application. Performance statistics can be queried
using the PROPERTY, DB_PROPERTY, and CONNECTION PROPERTY functions.

If the number of active requests is always less than the value of the -gn database server option, you can consider lowering
the multiprogramming level, but you must take into account the effects of intra-query parallelism, which adds additional tasks
to the database server's execution queues. If the effect of intra-query parallelism is marginal, lowering the multiprogramming
level can be done safely without reducing overall system throughput. However, if the number of total requests (active + unscheduled)
is often larger than the value specified by -gn, then an increase in the multiprogramming level may be warranted. You should
consider the performance tradeoffs of increasing the multiprogramming level before changing it.