Several settings can affect database server performance, including:
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 database server option|
|-ca 0||Enforces a static cache size||-ca database server option|
|-ch||Sets the maximum cache size for automatic cache resizing||-ch database 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 database server option|
|-cl||Sets the minimum cache size for automatic cache resizing||-cl database server option|
|-cs||Displays statistics about dynamic cache size changes in the database server messages window||-cs database 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 database server option|
|-cr||Warms the cache with database pages||-cr database server option|
|-cv||Displays messages about cache warming in the database server messages window||-cv database server option|
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 database server option||CurrentMultiProgrammingLevel||Sets the multiprogramming level of the database server.|
|-gna database server option||AutoMultiProgrammingLevel||Turns on and off dynamic tuning of the database server's multiprogramming level.|
|-gnh database server option||MaxMultiprogrammingLevel||Sets the maximum number of tasks that the database server can execute concurrently.|
|-gnl database server option||MinMultiProgrammingLevel||Sets the minimum number of tasks that the database server can execute concurrently.|
|-gns database server option||AutoMultiProgrammingLevelStatistics||Controls whether statistics about the automatic changes to the multiprogramming level appear in the database server message log.|
|-gta database server option||ProcessorAffinity||Instructs the database server which logical processors to use on Windows or Linux.|
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 database 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.
Discuss this page in DocCommentXchange.
|Copyright © 2014, SAP AG or an SAP affiliate company. - SAP Sybase SQL Anywhere 16.0|