Dela via

ALTER SERVER CONFIGURATION (Transact-SQL)

  • Artikel

Modifies global configuration settings for the current server in SQL Server 2012.

Topic link icon Transact-SQL Syntax Conventions

Syntax

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 }

Arguments

<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> ::=

  • 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 <\MSSQL\Log> 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> ::=

  • 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> ::=

  • HADR CLUSTER CONTEXT = { 'remote_windows_cluster' | LOCAL }
    Switches the HADR cluster context of the server instance to the specified Windows Server Failover Clustering (WSFC) cluster. The HADR cluster context determines what Windows Server Failover Clustering (WSFC) cluster 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 AlwaysOn Availability Groups to an instance of SQL Server 2012 SP1 on a new WSFC cluster.

    You can switch the HADR cluster context only from the local WSFC cluster to a remote cluster and then back from the remote cluster to the local cluster. 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 cluster object name (CON) of a WSFC cluster. 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.

    • LOCAL
      The local WSFC cluster.

    For more information, see Change the HADR Cluster Context of Server Instance (SQL Server).

General Remarks

This statement does not require a restart of SQL Server. In the case of a SQL Server failover cluster instance, it does not require a restart of the SQL Server cluster resource.

Limitations and Restrictions

This statement does not support DDL triggers.

Permissions

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.

Examples

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 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';

See Also

Tasks

Configure SQL Server to Use Soft-NUMA (SQL Server)

Reference

sys.dm_os_schedulers (Transact-SQL)

sys.dm_os_memory_nodes (Transact-SQL)

Concepts

Change the HADR Cluster Context of Server Instance (SQL Server)