ALTER SERVER PARAMETER (TSQL Statement)

alters the server parameters.

Syntax

ALTER SERVER PARAMETER SET <param_spec>

<param_spec> ::=
{
    SERVER_SERVERNAME                 = 'server_name'
  | SERVER_WORKERS_MAX                = workers_max
  | SERVER_GLOBAL_PAGE_CACHE_MEMORY   = global_cache_memory_size
  | SERVER_DEFAULT_DATABASE           = default_database
  | SERVER_DEFAULT_LANGUAGE           = default_language
  | SERVER_QUOTED_IDENTIFIER          = { ON|OFF }
  | SERVER_BULK_DIR                   = 'bulk_dir'
  | SERVER_DUMP_DIR                   = 'dump_dir'
  | SERVER_READ_TIMEOUT               = read_timeout
  | SERVER_LOCK_TICKER_INTERVAL       = lock_ticker_interval
  | SERVER_LOCK_TIMEOUT_TICKS_COUNT   = lock_timeout_ticks_count
  | SERVER_LOGGING_MAX_SIZE           = logging_max_size
  | SERVER_LOGGING_MAX_COUNT          = logging_max_count
  | SERVER_LOGGING_LOCALTIME          = logging_localtime
  | SERVER_WCACHE_MEMORY_MAX          = wcache_mem_size
  | SERVER_WCACHE_MODIF_MAX           = wcache_modif_size
}

Arguments

SERVER_SERVERNAME = ‘server_name’

server_name is a name of your choice, e.g. ‘jupiter/Accounting’.

  • It is the combination of a machine name and a service name, separated by a slash /.
  • The machine name can be any name. By default, it is the name of the machine on which RSQL is running.
  • The service name is any name describing the instance. By default, it is the name of the base directory of the instance.
  • See @@SERVERNAME and @@SERVICENAME.

This parameter takes effect after server restart.

SERVER_WORKERS_MAX = workers_max

workers_max is the maximum number of concurrent workers, in the range 1 to 64 (64 is the default at installation).

This parameter takes effect after server restart.

SERVER_GLOBAL_PAGE_CACHE_MEMORY = global_cache_memory_size

global_cache_memory_size is the size in MB of the global page cache.

  • RSQL can only work with pages that are present in the global page cache or in the session’s private write cache.
  • The size of this cache should be a small portion of the available RAM (10% is good. If your RAM is huge, you can even try with a smaller size).
  • When a page is ejected from this global page cache, it remains for a while in the Linux system cache.

So, if global_cache_memory_size is defined too large, a page will be kept in memory twice (in the global page cache and in the Linux system cache), which is bad for performance.

This parameter takes effect after server restart.

SERVER_DEFAULT_DATABASE = default_database

default_database is the default database for CREATE LOGIN.

This parameter takes effect immediately.

SERVER_DEFAULT_LANGUAGE = default_language

default_language is the default language for CREATE LOGIN.

This parameter takes effect immediately.

SERVER_QUOTED_IDENTIFIER = { ON|OFF }

specifies if identifiers can be delimited by double-quotes.

This parameter takes effect for the next script.

SERVER_BULK_DIR = ‘bulk_dir’

is the default directory for files imported by BULK INSERT or exported by BULK EXPORT.

  • The directory can be an absolute path or a relative path. A relative path is relative to the base directory of the RSQL instance.
  • By default, it is the directory bulkdir in the instance base directory.

This parameter takes effect immediately.

SERVER_DUMP_DIR = ‘dump_dir’

is the default directory for files files generated by BACKUP or restored by RESTORE.

  • The directory can be an absolute path or a relative path. A relative path is relative to the base directory of the RSQL instance.
  • By default, it is the directory dumpdir in the instance base directory.

This parameter takes effect immediately.

SERVER_READ_TIMEOUT = read_timeout

is the timeout in seconds that the server waits for client’s requests and keepalive messages.

  • If the client doesn’t send a message before this duration has elapsed, the connection is closed.
  • The value of read_timeout should be a little greater than the interval at which the client sends the keepalive messages.
  • A client sends keepalive messages every 20 seconds to indicate that it is still alive. So, allowing for some delay, read_timeout should be 30 seconds or more.
  • During long-running queries, if no keepalive is received from the client, the connection is closed. This way, locks and resources held by lost connections are freed.
  • If read_timeout is too large, the server could wait too long for the client’s login message.

This parameter takes effect after server restart.

SERVER_LOCK_TICKER_INTERVAL = lock_ticker_interval

specifies the interval in milliseconds of the lock manager timeout ticker.

  • The default value is 1000 ms (1 second). There is no reason to change this value.

This parameter takes effect after server restart.

SERVER_LOCK_TIMEOUT_TICKS_COUNT = lock_timeout_ticks_count

is the count of intervals of the lock manager timeout ticker, before a connection waiting for locks receives a timeout error.

This parameter takes effect after server restart.

SERVER_LOGGING_MAX_SIZE = logging_max_size

when the size of the logging file reaches this value in MB, the rotation occurs.

  • If 0, the logging file grows indefinitely.
  • By default, it is 5 (MB).

This parameter takes effect after server restart.

SERVER_LOGGING_MAX_COUNT = logging_max_count

specifies the number of old logging files to keep.

  • The logging files in excess are automatically deleted.
  • If 0, old logging files are never deleted.
  • By default, it is 10.

This parameter takes effect after server restart.

SERVER_LOGGING_LOCALTIME = logging_localtime

specifies if the timestamp appended to the logging file name when rotated is in local time or UTC.

  • By default, it is in local time.

This parameter takes effect after server restart.

SERVER_WCACHE_MEMORY_MAX = wcache_mem_size

Each session has its own private write cache, used to store the pages it is modifying but not committed yet.

  • The private cache size increases automatically as more pages are modified.
  • The private cache stops growing when it reaches wcache_mem_size, which is the maximum size in MB.
  • If more pages are being modified than the private cache keep store in RAM, older pages are spilled to the logfile.
  • The default value is good for most usage, in my opinion.

This parameter takes effect after server restart.

SERVER_WCACHE_MODIF_MAX = wcache_modif_size

wcache_modif_size is the limit in MB of all the modifications a transaction can perform before committing.

  • This limit includes the modifications on tables as well as their indexes.
  • If this limit is exceeded, an error is returned.

This parameter takes effect after server restart.

Remarks

This statement doesn’t exist in MS SQL Server.

Only sa is allowed to execute this statement.

Most parameters take effect only after a server restart.

The command SHOW P displays the server parameters.

The listening address cannot be specified with the ALTER SERVER PARAMETER statement.

Examples

ALTER SERVER PARAMETER SET SERVER_SERVERNAME = 'jupiter/Accounting';

ALTER SERVER PARAMETER SET SERVER_QUOTED_IDENTIFIER = OFF;

ALTER SERVER PARAMETER SET SERVER_BULK_DIR = '/home/john/mybulkdir';

ALTER SERVER PARAMETER SET SERVER_DEFAULT_DATABASE = accounting;
ALTER SERVER PARAMETER SET SERVER_DEFAULT_LANGUAGE = fr_fr;