Quick one today:

Recently, during troubleshooting an issue with one of our customers, some patterns emerged that indicated the possibility of resource contention for their repeated timeout issues. Upon further digging in, the CPU usage was abnormal. The database server was on a Virtual Machine with 8 processors allocated. The CPU usage was only on one processor. The rest were idle. 0% usage.

As part of that experience some lessons were gathered out of which today’s post emerges on CPU Affinity mask.

CPU Affinity Mask:

Sql Server uses all CPUs available through the Operating System. It creates schedulers on all the CPUs to make the best utilization of the resources for any given workload. Operating Systems, as part of its multitasking, it needs to switch process threads from processor to another. This is usually not detrimental for normal operations; But resource intensive operations like Sql Server this undercuts performance. Such Context Switching is not always a welcome scenario. To minimize we could configure the processors in a way that all the Sql Server load will be directed to a pre-selected group of processors. This is achieved using CPU Affinity Mask.

As you imagine, this is a HEX value. But we convert the HEX value into integer and configure Sql Server to lean towards (affinitize) a group of CPUs.

Let’s imagine a machine with 16 processors; When you assign all the processors to Sql Server and allow it to optimally utilize all the resources the settings look like this:

CPU Affinity Mask (default)

The options on the top convey that we are enabling Sql Server to fully manage the resource allocation with all the CPUs.

• Automatically set processor affinity mask for all processors
• Automatically set I/O affinity mask for all processors

I/O tasks like Lazywriter, CHECKPOINT, Log Writer, etc are directly affected by the dedicated CPUs allocated for I/O activities.

NUMA Noode0 and NUMA Node 1 are the CPU groups (either Hardware or Software configuration) to enhance hardware resource consumption. Each NUMA node has multiple CPUs with dedicated memory.

The ‘Processor Affinity’ Check box allows you to select individual CPUs, from each NUMA nodes, to SQL Server. Since the check box is not checked, none of the CPUs (from that NUMA node) have their affinity set (not allocated primarily for Sql Server).

Now, if you want to select all the processors from NUMA Node 0 to Sql Server, select that Check box. If you want to accomplish the same with T-SQL scripts like this:

Affinity Mask for each processor could be set using a 1 or 0 bit flag. 1 signifies the affinity being set. So each processor has an affinity mask bit flag that controls its affinity.

Let’s says you want to allocate all the 8 CPUs in NUMA Node 0 to Sql Server; First 4 with CPU affinity and last 4 for I/O affinity. This are the steps to follow:

Imagine setting 1 (bit) for each allocated CPU; Then convert that value into INTEGER.

In Binary the result looks like this 00001111; It translates to 0X0F in HEX. Which is 15 in integer. This is the value you want to set for ‘affinity mask’.

Now, let’s try the same logic for I/O affinity mask. Let’s set the last 4 bits to 1.

So, 11110000 in binary translates to 0xF0 in HEX and 240 in integer.

Putting it all together it becomes:

After setting CPU Affinity Mask

Since Following is the table from MSDN that shows other example:

These are affinity mask values for an 8-CPU system.

Some more points:

• One byte (8 bits) covers affinity mask for 8 CPUs. And two bytes for 16 CPUs, 3 bytes for 24; 4 for 32 CPUs.
• For more than 32 processors, you want to use affinity64 for another 32 processors.
• It is recommended not to use the same CPU for both CPU affinity and I/O affinity.
• Changing CPU affinity mask does not require restart.
• Any schedulers assigned to those CPUs will be shutdown once the current workload is completed. Any new workload, is assigned to new schedulers (or the assigned CPUs)

For more information read MSDN BoL article.