Configuring the database server's 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 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 database server's multiprogramming level
-gnh	MaxMultiprogrammingLevel	Sets the maximum number of tasks that the database server can execute concurrently
-gnl	MinMultiProgrammingLevel	Sets the minimum number of tasks that the database server can execute concurrently

Tuning the multiprogramming level

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 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 database server throughput level, then the database server proceeds with the increase. If the increase in the multiprogramming level results in degradation in throughput, then the database server lowers the multiprogramming level. The 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 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 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 database server also attempts to eliminate thread deadlock issues by automatically increasing the multiprogramming level each time a thread deadlock condition arises. The database server continues increasing the multiprogramming level up to the allowable MaxMultiprogrammingLevel value. Once the MaxMultiprogrammingLevel value is reached, the database server starts returning thread deadlock issues to client applications. See ThreadDeadlocksAvoided server property.
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.
The performance counters related to active and unscheduled requests should be observed during this analysis. See ActiveReq server property and UnschReq server property.

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. See Raising the multiprogramming level.

Configuring the database server's multiprogramming level

Tuning the multiprogramming level

See also