INF: Understanding How to Configure a SQL Server Connection Affinity Mask (299641)

MORE INFORMATION

SQL Server 2000 Service Pack 1 extends the functionality of the Virtual Interface Architecture (VIA) protocol by introducing the connection affinity mask option in the Server Network Utility. For more information about VIA, see SQL Server Books Online.

You can use the Server Network Utility to define protocols and ports that SQL Server will listen on. For the VIA transport, a network reader thread is created for each port defined. This thread can be restricted to run on a defined set of CPUs with a specified mask. The network thread will only distribute work items to schedulers running on the same set of CPUs as defined by the connection affinity mask.

If you do specify the connection affinity mask, you should use it in conjunction with the processor affinity mask system configuration option. The connection affinity mask should be a subset of the affinity mask configuration option. For more information about the affinity mask option, see SQL Server Books Online.

To set a connection affinity mask, follow these steps:

1. Start the Server Network Utility.
2. Click Enable and add VIA to the enabled protocols.
3. Click Property for the VIA protocol.
4. Under the Network Protocol Default Value Setup property, specify the Vendor (for instance, Giganet or Servernet).
5. In the Listen Info box, define the ports that you want SQL Server to listen on, by using the following syntax:

   <nic-number>:<VIA-port-number>[CPU mask],...

   for each network interface card (NIC) that needs to be configured.
   
   For example, the following

   0:1000[0x000f],0:1001[0x00f0],1:1000[0x0f00],1:1001[0xf000]
   						

   will give four network threads. Each thread will be restricted to four CPUs and will distribute its work items to the same four CPUs.

Please note the following additional points:

• The VIA port number is not the same as the TCP port number. This number needs to match up between the client and the server.
• The connection affinity mask is represented in hexadecimal (preceded by 0x or 0X).
• The mask is a bitmap in which the rightmost bit specifies the lowest-order CPU (0), the next rightmost bit specifies the next lowest-order CPU (1), and so on.
• A one-byte connection affinity mask covers up to eight CPUs in a multiprocessor computer, a two-byte mask covers up to 16 CPUs, a three-byte mask covers up to 24 CPUs, and a four-byte mask covers up to 32 CPUs.
• To enable connection affinity on a specific CPU, the bit corresponding to this CPU in the connection affinity mask should be set to 1.
• When all bits are set to 0, or when a connection affinity mask is not specified, SQL Server network I/O is scheduled to any of the CPUs that are eligible to process SQL Server threads.
• The value of the connection affinity mask cannot be changed while SQL Server is running. You should shut down and then restart the instance of SQL Server in order to have a new value specified for the connection affinity mask take effect.

The connection affinity mask can be used in following cases (although the final configuration depends on server specifications and subsequent tests):

• When SQL Server needs to handle a large number of network requests from clients over the VIA transport.
• To provide workload balancing and data locality.
  
  The work items from the connections behind a VIA port will be handled by a restricted number of CPUs as defined by the connection affinity mask. This allows you to partition the workload.
  
  For example, on an 16-CPU system, all batch jobs could be routed through a nic:port[cpu mask 0xf000] while the OLTP load could be routed through a separate nic:port[cpu mask 0x0fff]. In this scenario, SQL Server will use the first four CPUs to handle the batch load and the remainder to handle the OLTP load. Note that for this example, the affinity mask configuration option (sp_configure) should have been set to 0xffff.