Controlling performance and memory from the command line

Cache size The amount of cache memory available to the database server can be a key factor in affecting performance. The more memory made available to the database server, the faster it performs. The cache holds information that may be required more than once. Accessing information in cache is faster than accessing it from disk. The default initial cache size is computed based on the amount of physical memory, the operating system, and the size of the database files. The database server automatically adjusts the cache size as necessary. See Dynamic cache sizing.

The database server messages window displays the size of the cache at startup, and you can use the following statement to obtain the current size of the cache:

SELECT PROPERTY( 'CurrentCacheSize' );

For more information about performance tuning, see Performance monitoring and diagnostic tools.

The following table summarizes the database server options available for controlling the cache.

Cache feature	Database server option	Used for	See
Cache size	-c	Sets the initial amount of memory for the database server cache	-c dbeng12/dbsrv12 server option
	-ca 0	Enforces a static cache size	-ca dbeng12/dbsrv12 server option
	-ch	Sets the maximum cache size for automatic cache resizing	-ch dbeng12/dbsrv12 server option
	-chx	Sets the maximum cache size for automatic cache resizing without reserving address space for non-cache use (32-bit database servers only)	-chx dbeng12/dbsrv12 server option
	-cl	Sets the minimum cache size for automatic cache resizing	-cl dbeng12/dbsrv12 server option
	-cs	Displays statistics about dynamic cache size changes in the database server messages window	-cs dbeng12/dbsrv12 server option
Cache warming	-cc	Collects information about database pages that can be used for cache warming the next time the database is started	-cc dbeng12/dbsrv12 server option
	-cr	Warms the cache with database pages	-cr dbeng12/dbsrv12 server option
	-cv	Displays messages about cache warming in the database server messages window	-cv dbeng12/dbsrv12 server option
Address Windowing Extensions (AWE) cache (deprecated)	-cm	Sets the amount of address space allocated for an AWE cache on Windows	-cm dbeng12/dbsrv12 server option
	-cw	Enables the use of AWE on Windows	-cw dbeng12/dbsrv12 server option (deprecated)

Note

The use of AWE is deprecated. It is recommended that you use the 64-bit version of the SQL Anywhere database server on a 64-bit Windows operating system if you require a large cache.

Multiprogramming level The database server's multiprogramming level specifies the maximum number of database server tasks that can execute concurrently. In general, a higher multiprogramming level increases the overall throughput of the database server by permitting more requests to execute simultaneously. However, if the requests compete for the same resources, increasing the multiprogramming level can lead to additional contention and lengthen transaction response time.

By default, SQL Anywhere automatically adjusts the database server's multiprogramming level. In some cases you can lower the throughput of the system by increasing the multiprogramming level. The following options allow you to control the database server's multiprogramming level manually:

Database server option	sa_server_option value	Description
-gn dbsrv12 server option	CurrentMultiProgrammingLevel	Sets the multiprogramming level of the database server.
-gna dbsrv12 server option	AutoMultiProgrammingLevel	Turns on and off dynamic tuning of the database server's multiprogramming level.
-gnh dbsrv12 server option	MaxMultiprogrammingLevel	Sets the maximum number of tasks that the database server can execute concurrently.
-gnl dbsrv12 server option	MinMultiProgrammingLevel	Sets the minimum number of tasks that the database server can execute concurrently.
-gns dbsrv12 server option	AutoMultiProgrammingLevelStatistics	Controls whether statistics about the automatic changes to the multiprogramming level appear in the database server message log. You can also control this behavior by using the AutoMultiProgrammingLevelStatistics property with the sa_server_option system procedure. See sa_server_option system procedure.

For more information about the multiprogramming level in SQL Anywhere, see Database server configuration of the multiprogramming level.

Number of processors If you are running on a multi-processor computer using a network database server, you can set the number of processors with the -gt option. See -gt dbeng12/dbsrv12 server option and SQL Anywhere threading.

The number of CPUs that the database server can use may also be affected by your license or SQL Anywhere edition. See Editions and licensing.

Other performance-related options There are several options available for tuning network performance, including -gb (database process priority), and -u (buffered disk I/O). See SQL Anywhere database server syntax.