• Article

ALTER SERVER CONFIGURATION (Transact-SQL)

Associates hardware threads with CPUs in SQL Server 2008 R2.

Topic link iconTransact-SQL Syntax Conventions

Syntax

ALTER SERVER CONFIGURATION
SET 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 ]

Arguments

  • 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.

Remarks

This statement does not support DDL triggers. A restart of SQL Server is not required.

Permissions

Requires ALTER SETTINGS permission.

Examples

In the following examples, 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 though 3, CPUs 0 to 63

  • Group 1: NUMA nodes 4 though 7, CPUs 64 to 127

  • Group 2: NUMA nodes 8 though 12, CPUs 128 to 191

  • Group 3: NUMA nodes 13 though 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 shows how to set 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 shows how to set affinity AUTO.

ALTER SERVER CONFIGURATION 
SET PROCESS AFFINITY CPU=AUTO;