ALTER SERVER CONFIGURATION (Transact-SQL)
Modifies global configuration settings for the current server in SQL Server 2012.
ALTER SERVER CONFIGURATION
SET <optionspec>
<optionspec> ::=
{
<process_affinity>
| <diagnostic_log>
| <failover_cluster_property>
| <hadr_cluster_context>
}
<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 }
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.
The SQL Server Database Engine 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 |
|---|---|
|
CPU • NUMANODE • AUTO |
|
|
ON • OFF • PATH • MAX_SIZE |
|
|
HealthCheckTimeout |
|
|
' windows_cluster ' |
Setting process affinity
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.
-
Group 0: NUMA nodes 0 through 3, CPUs 0 to 63
-
Group 1: NUMA nodes 4 through 7, CPUs 64 to 127
-
Group 2: NUMA nodes 8 through 12, CPUs 128 to 191
-
Group 3: NUMA nodes 13 through 16, CPUs 192 to 255
A. Setting affinity to all CPUs in groups 0 and 2
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;
B. Setting affinity to all CPUs in NUMA nodes 0 and 7
The following example sets the CPU affinity to nodes 0 and 7 only.
ALTER SERVER CONFIGURATION SET PROCESS AFFINITY NUMANODE=0, 7;
C. Setting affinity to CPUs 60 through 200
The following example sets affinity to CPUs 60 through 200.
ALTER SERVER CONFIGURATION SET PROCESS AFFINITY CPU=60 TO 200;
D. Setting affinity to CPU 0 on a system that has two CPUs
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;
E. Setting affinity to AUTO
The following example sets affinity to AUTO.
ALTER SERVER CONFIGURATION SET PROCESS AFFINITY CPU=AUTO;
Setting diagnostic log options
The examples in this section show how to set the values for the diagnostic log option.
A. Starting diagnostic logging
The following example starts the logging of diagnostic data.
ALTER SERVER CONFIGURATION SET DIAGNOSTICS LOG ON;
B. Stopping diagnostic logging
The following example stops the logging of diagnostic data.
ALTER SERVER CONFIGURATION SET DIAGNOSTICS LOG OFF;
C. Specifying the location of the diagnostic logs
The following example sets the location of the diagnostic logs to the specified file path.
ALTER SERVER CONFIGURATION SET DIAGNOSTICS LOG PATH = 'C:\logs';
D. Specifying the maximum size of each diagnostic log
The following example set the maximum size of each diagnostic log to 10 megabytes.
ALTER SERVER CONFIGURATION SET DIAGNOSTICS LOG MAX_SIZE = 10 MB;
Setting failover cluster properties
The following example illustrates setting the values of the SQL Server failover cluster resource properties.
A. Specifying the value for the HealthCheckTimeout property
The following example sets the HealthCheckTimeout option to 15,000 milliseconds (15 seconds).
ALTER SERVER CONFIGURATION SET FAILOVER CLUSTER PROPERTY HealthCheckTimeout = 15000;
Changing the cluster context of an availability replica
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';
