Alter Configuration

ALTER CONFIGURATION changes one or more server settings. The running engine is updated and the settings are saved to the server's appsettings.json file, so they persist across restarts.

• Requires an account that is a member of an administrator role.
• Setting names are not case-sensitive. An unknown name fails with a parser error that lists the valid names.
• Some settings, such as the folders, the listen port and the thread pool sizes, are only read when the server starts and take effect after a restart.

Enables deferred IO and sets the maximum cache size.

ALTER CONFIGURATION WITH (DeferredIOEnabled = true, CacheMaxMemoryMegabytes = 8192)

The thread pool rows each describe several settings, for example LookupThreadPoolInitialSize and LookupThreadPoolMaximumSize.

Setting	Default	Description
CacheEnabled	true	Whether reads and writes are cached in memory.
CacheMaxMemoryMegabytes	4096	The memory, in megabytes, that the cache may use before it is scavenged.
CachePartitions	0	The number of memory cache partitions (0 = the number of CPUs).
CacheScavengeInterval	10	Seconds between cache scavenges, when the cache memory limit is enforced by ejecting the least used items.
CacheSeconds	3600	Seconds to keep an item in the cache (sliding expiration).
DeferredIOEnabled	false	When true, the writes of a transaction are deferred until it is committed.
TransactionLogPartitions	32	The number of partitions of the transaction log database.
LockWaitTimeoutSeconds	0	Seconds a transaction waits for a lock before timing out (0 = wait forever).
MaxIdleConnectionSeconds	60	Seconds a connection can be idle before its transaction is rolled back and it is disconnected.
LargeObjectHeapCompactionInterval	60	Seconds between checks for low activity before compacting the large object heap (0 = disabled).
HealthMonitoringEnabled	true	Whether the engine keeps health counters.
HealthMonitoringInstanceLevelEnabled	false	Whether to keep per-object counters. Useful, but has a serious impact on performance.
HealthMonitoringInstanceLevelTimeToLiveSeconds	600	Seconds that per-object counters are kept.
HealthMonitoringCheckpointSeconds	600	Seconds between writing the health counters to disk.
LookupThreadPoolInitialSize / MaximumSize	0 / 0	Initial and maximum threads of the document lookup pool (0 = automatic).
LookupThreadPoolQueueDepth / LookupChildThreadPoolQueueDepth	10000 / 5000	Maximum queued items of the lookup pool, and per operation.
IndexingThreadPoolInitialSize / MaximumSize	0 / 0	Initial and maximum threads of the indexing pool (0 = automatic).
IndexingThreadPoolQueueDepth / IndexingChildThreadPoolQueueDepth	10000 / 5000	Maximum queued items of the indexing pool, and per operation.
IntersectionThreadPoolInitialSize / MaximumSize	0 / 0	Initial and maximum threads of the join/intersection pool (0 = automatic).
IntersectionThreadPoolQueueDepth / IntersectionChildThreadPoolQueueDepth	10000 / 5000	Maximum queued items of the intersection pool, and per operation.
MaterializationThreadPoolInitialSize / MaximumSize	0 / 0	Initial and maximum threads of the result materialization pool (0 = automatic).
MaterializationThreadPoolQueueDepth / MaterializationChildThreadPoolQueueDepth	10000 / 100	Maximum queued items of the materialization pool, and per operation.
ListenPort	6858	The TCP/IP port the server listens on.
DataRootPath	.\data\root	The folder that contains all schemas.
TransactionDataPath	.\data\transaction	The folder that contains the transaction log.
LogDirectory	.\data\log	The folder for text and performance logs.
FlushLog	false	When true, the text log is flushed to disk on every write.
WriteTraceData	false	When true, the server writes very verbose information about almost every internal operation.

• ShowThreadPools
• ShowHealthCounters