Configuring SQL Server CPU Utilization

Introduction

SQL Server is a server application and it is usually deployed into a server machine which
has multiple CPU installed within.  Planning how SQL Server will use the CPU is essential
to SQL Server performance and throughput.

The ideal scenario of course is to put SQL Server process into a CPU that is not being use
by the operating server or any other system process.

 

Before We Begin.

To manage expectations, please assume that "Enterprise Edition" of SQL Server is the basis of discussion

A Quick Look at Instance Level Configuration

Below is a quick glance at instance level CPU configuration for SQL Server 2012. (Click image to enlarge)
 

Automatically Set Processor  Affinity Mask For All Process.

"An affinity mask is a bit mask indicating what processor(s) a thread or process should be run on
by the scheduler of an operating system."  Wikipedia

If this option is check. SQL Server will use all the CPU for processing. However, if there are cases when
you want to deliberately control CPU usage, You can uncheck this option and check only the CPU you
want to be utilized by SQL Server.

Automatically Set I/O Affinity Mask For All Process.

"The affinity I/O mask option binds SQL Server disk I/O to a specified subset of CPUs." Books Online
This option is used to configure which CPU will handle disk read and write. Similar to Affinity mask
checking this option will use all the CPU for read and write operation. Unchecking this will allow you to'
choose which CPU will be used for disk I/O operations.

Boost SQL Server Priority

"Use the priority boost option to specify whether Microsoft SQL Server should run at a higher Microsoft
Windows 2008 or Windows 2008 R2 scheduling priority than other processes on the same computer.
 If you set this option to 1, SQL Server runs at a priority base of 13 in the Windows 2008 or Windows
Server 2008 R2 scheduler. The default is 0, which is a priority base of 7." Books Online

This option will be remove in the future version of SQL Server. Please do not use this option in
future development.

SnapShot of Manually Configured CPU utilization

Below is a snapshot of a manually configured CPU utilization design.
 
 
In the above snapshot, where CPU utilizatilization is manually configured, notice that Process Affintiy and I/O affinity is scheduled on different CPU. Management studio will not allow you to configure I/O affinity and process affinity to be bounded on the same CPU.