Below is a quick glance at instance level CPU configuration for SQL Server 2012. (Click image to enlarge)
"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."
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.
"The affinity I/O mask option binds SQL Server disk I/O to a specified subset of CPUs."
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.
"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."
This option will be remove in the future version of SQL Server. Please do not use this option in
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
This article has been highlighted in this week's Top Contributors Blog : blogs.technet.com/.../top-contributors-awards-includes-windows-update-agent-sql-server-remote-management-and-other-stuff.aspx