Transact-SQL Syntax ConventionsModifies global configuration settings for the current server in SQL Server
Transact-SQL Syntax Conventions
ALTER SERVER CONFIGURATION
SET <optionspec>
[;]
<optionspec> ::=
{
<process_affinity>
| <diagnostic_log>
| <failover_cluster_property>
| <hadr_cluster_context>
| <buffer_pool_extension>
| <soft_numa>
}
<process_affinity> ::=
PROCESS AFFINITY
{
CPU = { AUTO | <CPU_range_spec> }
| NUMANODE = <NUMA_node_range_spec>
}
<CPU_range_spec> ::=
{ CPU_ID | CPU_ID TO CPU_ID } [ ,...n ]
<NUMA_node_range_spec> ::=
{ NUMA_node_ID | NUMA_node_ID TO NUMA_node_ID } [ ,...n ]
<diagnostic_log> ::=
DIAGNOSTICS LOG
{
ON
| OFF
| PATH = { 'os_file_path' | DEFAULT }
| MAX_SIZE = { 'log_max_size' MB | DEFAULT }
| MAX_FILES = { 'max_file_count' | DEFAULT }
}
<failover_cluster_property> ::=
FAILOVER CLUSTER PROPERTY <resource_property>
<resource_property> ::=
{
VerboseLogging = { 'logging_detail' | DEFAULT }
| SqlDumperDumpFlags = { 'dump_file_type' | DEFAULT }
| SqlDumperDumpPath = { 'os_file_path'| DEFAULT }
| SqlDumperDumpTimeOut = { 'dump_time-out' | DEFAULT }
| FailureConditionLevel = { 'failure_condition_level' | DEFAULT }
| HealthCheckTimeout = { 'health_check_time-out' | DEFAULT }
}
<hadr_cluster_context> ::=
HADR CLUSTER CONTEXT = { 'remote_windows_cluster' | LOCAL }
<buffer_pool_extension>::=
BUFFER POOL EXTENSION
{ ON ( FILENAME = 'os_file_path_and_name' , SIZE = <size_spec> )
| OFF }
<size_spec> ::=
{ size [ KB | MB | GB ] }
<soft_numa> ::=
SET SOFTNUMA
{ ON | OFF } <process_affinity> ::=
PROCESS AFFINITY
Enables hardware threads to be associated with CPUs.
CPU = { AUTO | <CPU_range_spec> }
Distributes SQL Server worker threads to each CPU within the specified range. CPUs outside the specified range will not have assigned threads.
AUTO
Specifies that no thread is assigned a CPU. The operating system can freely move threads among CPUs based on the server workload. This is the default and recommended setting.
<CPU_range_spec> ::=
Specifies the CPU or range of CPUs to assign threads to.
{ CPU_ID | CPU_ID TO CPU_ID } [ ,…n ]
Is the list of one or more CPUs. CPU IDs begin at 0 and are integer values.
NUMANODE = <NUMA_node_range_spec>
Assigns threads to all CPUs that belong to the specified NUMA node or range of nodes.
<NUMA_node_range_spec> ::=
Specifies the NUMA node or range of NUMA nodes.
{ NUMA_node_ID | NUMA_node_ID TO NUMA_node_ID } [ ,…n ]
Is the list of one or more NUMA nodes. NUMA node IDs begin at 0 and are integer values.
<diagnostic_log> ::=
Applies to: SQL Server 2012 (11.x) through SQL Server 2012 (11.x) SQL Server 2017
DIAGNOSTICS LOG
Starts or stops logging diagnostic data captured by the sp_server_diagnostics procedure, and sets SQLDIAG log configuration parameters such as the log file rollover count, log file size, and file location. For more information, see View and Read Failover Cluster Instance Diagnostics Log.
ON
Starts SQL Server logging diagnostic data in the location specified in the PATH file option. This is the default.
OFF
Stops logging diagnostic data.
PATH = { ‘os_file_path’ | DEFAULT }
Path indicating the location of the diagnostic logs. The default location is <> within the installation folder of the SQL Server failover cluster instance.
MAX_SIZE = { ‘log_max_size’ MB | DEFAULT }
Maximum size in megabytes to which each diagnostic log can grow. The default is 100 MB.
MAX_FILES = { ‘max_file_count’ | DEFAULT }
Maximum number of diagnostic log files that can be stored on the computer before they are recycled for new diagnostic logs.
<failover_cluster_property> ::=
Applies to: SQL Server 2012 (11.x) through SQL Server 2012 (11.x) SQL Server 2017
FAILOVER CLUSTER PROPERTY
Modifies the SQL Server resource private failover cluster properties.
VERBOSE LOGGING = { ‘logging_detail’ | DEFAULT }
Sets the logging level for SQL Server Failover Clustering. It can be turned on to provide additional details in the error logs for troubleshooting.
0 – Logging is turned off (default)
1 - Errors only
2 – Errors and warnings
SQLDUMPEREDUMPFLAGS
Determines the type of dump files generated by SQL Server SQLDumper utility. The default setting is 0. For more information, see SQL Server Dumper Utility Knowledgebase article.
SQLDUMPERDUMPPATH = { ‘os_file_path’ | DEFAULT }
The location where the SQLDumper utility stores the dump files. For more information, see SQL Server Dumper Utility Knowledgebase article.
SQLDUMPERDUMPTIMEOUT = { ‘dump_time-out’ | DEFAULT }
The time-out value in milliseconds for the SQLDumper utility to generate a dump in case of a SQL Server failure. The default value is 0, which means there is no time limit to complete the dump. For more information, see SQL Server Dumper Utility Knowledgebase article.
FAILURECONDITIONLEVEL = { ‘failure_condition_level’ | DEFAULT }
Tthe conditions under which the SQL Server failover cluster instance should failover or restart. The default value is 3, which means that the SQL Server resource will failover or restart on critical server errors. For more information about this and other failure condition levels, see Configure FailureConditionLevel Property Settings.
HEALTHCHECKTIMEOUT = { ‘health_check_time-out’ | DEFAULT }
The time-out value for how long the SQL Server Database Engine resource DLL should wait for the server health information before it considers the instance of SQL Server as unresponsive. The time-out value is expressed in milliseconds. The default is 60000 milliseconds (60 seconds).
<hadr_cluster_context> ::=
Applies to: SQL Server 2012 (11.x) through SQL Server 2012 (11.x) SQL Server 2017
HADR CLUSTER CONTEXT = { ‘remote_windows_cluster’ | LOCAL }
Switches the HADR cluster context of the server instance to the specified Windows Server Failover Cluster (WSFC). The HADR cluster context determines what WSFC manages the metadata for availability replicas hosted by the server instance. Use the SET HADR CLUSTER CONTEXT option only during a cross-cluster migration of Always On availability groups to an instance of Always On availability groups SQL Server 2012 SP1 (11.0.3x) or higher version on a new WSFC r.
You can switch the HADR cluster context only from the local WSFC to a remote WSFC and then back from the remote WSFC to the local WSFC. The HADR cluster context can be switched to a remote cluster only when the instance of SQL Server is not hosting any availability replicas.
A remote HADR cluster context can be switched back to the local cluster at any time. However, the context cannot be switched again as long as the server instance is hosting any availability replicas.
To identify the destination cluster, specify one of the following values:
windows_cluster
The netwirj name of a WSFC. You can specify either the short name or the full domain name. To find the target IP address of a short name, ALTER SERVER CONFIGURATION uses DNS resolution. Under some situations, a short name could cause confusion, and DNS could return the wrong IP address. Therefore, we recommend that you specify the full domain name.
[!NOTE] A cross-cluster migration using this setting is no longer supported. To perform a cross-cluster migration, use a Distributed Availability Group or some other method such as log shipping.
LOCAL
The local WSFC.
For more information, see Change the HADR Cluster Context of Server Instance (SQL Server).
<buffer_pool_extension>::=
Applies to: SQL Server 2014 (12.x) through SQL Server 2014 (12.x) SQL Server 2017
ON
Enables the buffer pool extension option. This option extends the size of the buffer pool by using nonvolatile storage such as solid-state drives (SSD) to persist clean data pages in the pool. For more information about this feature, see Buffer Pool Extension.The buffer pool extension is not available in every SQL Server edition. For more information, see Editions and Supported Features for SQL Server 2016.
FILENAME = ‘os_file_path_and_name’
Defines the directory path and name of the buffer pool extension cache file. The file extension must be specified as .BPE. You must turn off BUFFER POOL EXTENSION before you can modify FILENAME.
SIZE = size [ KB | MB | GB ]
Defines the size of the cache. The default size specification is KB. The minimum size is the size of Max Server Memory. The maximum limit is 32 times the size of Max Server Memory. For more information about Max Server Memory, see sp_configure (Transact-SQL).
You must turn BUFFER POOL EXTENSION off before you can modify the size of the file. To specify a size that is smaller than the current size, the instance of SQL Server must be restarted to reclaim memory. Otherwise, the specified size must be the same as or larger than the current size.
OFF
Disables the buffer pool extension option. You must disable the buffer pool extension option before you modify any associated parameters such as the size or name of the file. When this option is disabled, all related configuration information is removed from the registry.
[!WARNING]
Disabling the buffer pool extension might have a negative impact server performance because the buffer pool is significantly reduced in size.
<soft_numa>
Applies to: SQL Server 2017 through SQL Server 2017 SQL Server 2017
ON
Enables automatic partitioning to split large NUMA hardware nodes into smaller NUMA nodes. Changing the running value requires a restart of the database engine.
OFF
Disables automatic software partitioning of large NUMA hardware nodes into smaller NUMA nodes. Changing the running value requires a restart of the database engine.
[!WARNING]
There are known issues with the behavior of the ALTER SERVER CONFIGURATION statement with the SOFT NUMA option and SQL Server Agent. The following is the recommended sequence of operations:
1) Stop the instance of SQL Server Agent.
2) Execute your ALTER SERVER CONFGURATION SOFT NUMA option.
3) Re-start the SQL Server instance.
4) Start the instance of SQL Server Agent.
More Information: If an ALTER SERVER CONFIGURATION with SET SOFTNUMA command is executed before the SQL Server service is restarted, then when the SQL Server Agent service is stopped, it will execute a T-SQL RECONFIGURE command that will revert the SOFTNUMA settings back to what they were before the ALTER SERVER CONFIGURATION.
This statement does not require a restart of SQL Server unless explicitly stated otherwise. In the case of a SQL Server SQL Server failover cluster instance, it does not require a restart of the SQL Server SQL Server SQL Server cluster resource.
This statement does not support DDL triggers.
Requires ALTER SETTINGS permissions for the process affinity option. ALTER SETTINGS and VIEW SERVER STATE permissions for the diagnostic log and failover cluster property options, and CONTROL SERVER permission for the HADR cluster context option.
Requires ALTER SERVER STATE permission for the buffer pool entension option.
The SQL Server ssDE] resource DLL runs under the Local System account. Therefore, the Local System account must have read and write access to the specified path in the Diagnostic Log option.
| Category | Featured syntax elements |
|---|---|
| Setting process affinity | CPU • NUMANODE • AUTO |
| Setting diagnostic log options | ON • OFF • PATH • MAX_SIZE |
| Setting failover cluster properties | HealthCheckTimeout |
| Changing the cluster context of an availability replica | ’ windows_cluster ’ |
| Setting the buffer pool extension | BUFFER POOL EXTENSION |
The examples in this section show how to set process affinity to CPUs and NUMA nodes. The examples assume that the server contains 256 CPUs that are arranged into four groups of 16 NUMA nodes each. Threads are not assigned to any NUMA node or CPU.
The following example sets affinity to all the CPUs in groups 0 and 2.
ALTER SERVER CONFIGURATION
SET PROCESS AFFINITY CPU=0 TO 63, 128 TO 191; The following example sets the CPU affinity to nodes 0 and 7 only.
ALTER SERVER CONFIGURATION
SET PROCESS AFFINITY NUMANODE=0, 7; The following example sets affinity to CPUs 60 through 200.
ALTER SERVER CONFIGURATION
SET PROCESS AFFINITY CPU=60 TO 200; The following example sets the affinity to CPU=0 on a computer that has two CPUs. Before the following statement is executed the internal affinity bitmask is 00.
ALTER SERVER CONFIGURATION SET PROCESS AFFINITY CPU=0; The following example sets affinity to AUTO.
ALTER SERVER CONFIGURATION
SET PROCESS AFFINITY CPU=AUTO; Applies to: SQL Server 2012 (11.x) through SQL Server 2012 (11.x) SQL Server 2017
The examples in this section show how to set the values for the diagnostic log option.
The following example starts the logging of diagnostic data.
ALTER SERVER CONFIGURATION SET DIAGNOSTICS LOG ON; The following example stops the logging of diagnostic data.
ALTER SERVER CONFIGURATION SET DIAGNOSTICS LOG OFF; The following example sets the location of the diagnostic logs to the specified file path.
ALTER SERVER CONFIGURATION
SET DIAGNOSTICS LOG PATH = 'C:\logs'; The following example set the maximum size of each diagnostic log to 10 megabytes.
ALTER SERVER CONFIGURATION
SET DIAGNOSTICS LOG MAX_SIZE = 10 MB; Applies to: SQL Server 2012 (11.x) through SQL Server 2012 (11.x) SQL Server 2017
The following example illustrates setting the values of the SQL Server failover cluster resource properties.
The following example sets the HealthCheckTimeout option to 15,000 milliseconds (15 seconds).
ALTER SERVER CONFIGURATION
SET FAILOVER CLUSTER PROPERTY HealthCheckTimeout = 15000; The following example changes the HADR cluster context of the instance of SQL Server To specify the destination WSFC cluster, clus01, the example specifies the full cluster object name, clus01.xyz.com.
ALTER SERVER CONFIGURATION SET HADR CLUSTER CONTEXT = 'clus01.xyz.com'; Applies to: SQL Server 2014 (12.x) through SQL Server 2014 (12.x) SQL Server 2017
The following example enables the buffer pool extension option and specifies a file name and size.
ALTER SERVER CONFIGURATION
SET BUFFER POOL EXTENSION ON
(FILENAME = 'F:\SSDCACHE\Example.BPE', SIZE = 50 GB); The following example modifies the size of a buffer pool extension file. The buffer pool extension option must be disabled before any of the parameters are modified.
ALTER SERVER CONFIGURATION
SET BUFFER POOL EXTENSION OFF;
GO
EXEC sp_configure 'max server memory (MB)', 12000;
GO
RECONFIGURE;
GO
ALTER SERVER CONFIGURATION
SET BUFFER POOL EXTENSION ON
(FILENAME = 'F:\SSDCACHE\Example.BPE', SIZE = 60 GB);
GO
Soft-NUMA (SQL Server)
Change the HADR Cluster Context of Server Instance (SQL Server)
sys.dm_os_schedulers (Transact-SQL)
sys.dm_os_memory_nodes (Transact-SQL)
sys.dm_os_buffer_pool_extension_configuration (Transact-SQL)
Buffer Pool Extension