Wiki  >  TechNet Articles  > 

Configuring SQL Server CPU Utilization - TechNet Articles - United States (English) - TechNet Wiki

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.

Table of Contents

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 essentialerarchy-list">
  • Introduction
  • Before We Begin.
  • A Quick Look at Instance Level Configuration
  • Automatically Set Processor Affinity Mask For All Process.
  • Automatically Set I/O Affinity Mask For All Process.
  • Boost SQL Server Priority
  • SnapShot of Manually Configured CPU utilization

    •  

    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.