Blog

How much CPU does Microsoft’s SQL Server 2016 use?

July 26, 2017 | Susan Bilder
Circuits from a mother board.jpegRunning Microsoft SQL Server 2016 without enough CPU resources will degrade performance as badly as trying to run SQL Server without enough memory. Determining how much CPU is available for a SQL Server involves:
 
  1. Inspecting licensing agreements and NUMA configurations to determine if there are any limitations on available CPUs
  2. Evaluating which scenario is a best fit in terms of usage, configuration and licensing costs
  3. Monitoring performance indicators to determine if SQL Server has enough resources for optimal performance.

Licensing: How many cores can you use?

Microsoft SQL Server 2016 licensing is based on both SQL edition and licensing options (per core or Server + CAL). If licensed by core, licenses are sold in packs of 2, with a minimum of 4 core licenses per socket, and the maximum number of CPU cores is based on the SQL Server edition, with the following limits for single instances of the Database Engine, Analysis Services (AR), or Reporting Services (RS) components :

Edition Enterprise and Developer editions Standard edition Web edition Express edition
Maximum number of cores Operating system maximum Lesser of 4 sockets or 24 cores Lesser of 4 sockets or 16 cores Lesser of 1 socket or 4 cores

Pricing is available online, but, as always, contact your reseller for specific pricing. Also, keep in mind that there are limitations to the free Developer and Express editions. Developer edition has the same capabilities as Enterprise, but the licensing agreement limits the ways in which you can use Developer edition. The Express edition does not have the usage limits that Developer edition does, but it does have limits on scale and functionality.

Core licensing: The fine print

  1. How many cores do you need to license?

    On a physical server running SQL Server, all the cores on the server must be licensed for SQL, even if that is more CPU capacity than your SQL instance requires. On a VM, you only need to license the logical CPUs allocated to the VM, with a minimum of 4 license units.
  2. Does hyperthreading count for licensing?

    Physical servers do not count hyperthreading when licensing SQL server. Licensing is based only on the number of physical cores on the server.

    For virtualized SQL servers, licensing is based on the logical CPUs assigned to the VM running SQL Server. If the logical CPUs are from virtualization servers that are hyperthreaded, then you are licensing based on hyperthreaded CPUs. If the virtualization server CPUs are not hyperthreaded, then you’re licensing based on physical CPUs.
  3. “Lesser of” sockets or cores

    The Standard, Web and Express editions of SQL 2016 are limited to a set number of sockets or cores, whichever is smaller. On a VM, a logical CPU counts as a socket - so, for a Standard edition license, you would be limited to 4 logical CPUs. However, it is possible to use Virtual NUMA in Hyper-V or vNUMA in VMware to provide a virtual socket/core architecture to the VM, allowing you to license more logical CPUs for SQL.

    For physical servers, as mentioned previously, all the cores must be licensed. This is true even if they exceed the maximum number of cores allowed for the SQL server edition. So, if you have a 4 socket server with 8 cores per socket, you would need to license all 32 cores, but Standard edition would only be able to use 24 of the cores. If you’re not utilizing all the cores on a physical server, and the server uses NUMA, check to make sure that the cores you are utilizing are balanced equally across the NUMA nodes on the system.

Soft NUMA: Scaling SQL to work with large NUMA nodes

In a previous post we discussed NUMA (non-uniform memory access) and VMware. Processors and RAM are connected via a system bus, and, before NUMA, all the processors were connected to all the RAM via one central bus. With NUMA, the system architecture is divided into multiple nodes, with each node containing one or more processors connected via their own bus to a set of local RAM. The NUMA nodes have busses connecting them, so processors can still access RAM in other NUMA nodes, but access to RAM on the local NUMA node is faster and NUMA aware software takes advantage of this.

One of the ways that SQL Server takes advantage of fast local memory access in NUMA architecture is by using parallelism to run queries over multiple processors in the same NUMA node. The maximum degree of parallelism (maxdop) is the maximum number of processors that can be used for a query. If a NUMA node has 8 or more processors, the guideline for maxdop = 8. If the NUMA node has less than 8 processors, then the guideline is to set maxdop <= the number of processors on the node.

The value of 8 for maxdop was set when SQL Server 2005 support for NUMA was being developed. Advances in technology have pushed the number of processors per NUMA node to much higher levels, but SQL NUMA support does not scale well beyond 8 processors. In order to optimize SQL performance on NUMA nodes with more than 8 processors, SQL Server 2016 uses Soft NUMA.

Soft NUMA is automatically configured for SQL Server 2014 SP2 and SQL Server 2016. Soft NUMA works by creating logical NUMA nodes either within larger NUMA nodes or on a system without NUMA. Soft NUMA doesn’t map local memory to processor nodes in the way that hardware NUMA does, but it does group the processors together as a unit, which can reduce I/O and alleviate lazy write bottlenecks.

SQL Server 2016 Configuration Scenarios

  1. Physical server with NUMA enabled with 1 socket/NUMA node. 4 sockets total with 16 cores/socket:
    • Enterprise edition:
      • Purchase: 64 core licenses
      • Cores: all cores on all sockets in use
      • Soft NUMA: 2 soft NUMA nodes with 8 cores per physical NUMA node.
    • Standard edition:
      • Purchase: 64 core licenses
      • Cores: Uses 24 cores (check that cores are allocated across all NUMA nodes)
      • Soft NUMA: 6 cores/NUMA node if cores are balanced, so not needed
    • SQL Express:
      • Purchase: Free
      • Cores: 4 cores on one socket in use.
      • Soft NUMA: 4 cores, so not needed
  2. Largest available SQL Server 2016 VMs running on virtualization host with hyperthreading enabled. NUMA enabled with 1 socket/node, 4 sockets, 16 cores/socket.
    • Enterprise edition:
      • Purchase: 128 core licenses
      • VMs in use: 1
      • Cores: all cores in use.
      • Soft NUMA: 4 soft NUMA nodes with 8 logical CPUs per physical NUMA node.
    • Standard edition w/ vNUMA:
      • Purchase: 124 core licenses (vNUMA configured with 4 sockets/6 cores each)
      • VMs in use: 5
      • Cores: 5 VMs with 24 cores each
      • Soft NUMA: 6 cores/NUMA node, so not needed.
    • Standard edition without vNUMA:
      • Purchase: 128 core licenses
      • VMs in use: 32
      • Cores: 32 VMs with 4 logical CPUs each
      • Soft NUMA: 4 logical CPUs, so not needed.
    • SQL Express w/ vNUMA:
      • Purchase: free
      • VMs in use: 32
      • Cores: 32 VMs with 4 cores each
      • Soft NUMA: 4 cores/NUMA node, so not needed
    • SQL Express without vNUMA:
      • Purchase: free
      • VMs in use: 128
      • Cores: 128 VMs with 1 logical CPU each
      • Soft NUMA: not needed

SQL CPU Key Performance Indicators

Once SQL Server is up and running, you need to evaluate its performance to determine if more CPU resources are required. The following key performance indicators (KPIs) can be used to detect CPU performance problems on a SQL Server:

  • CPU Ready (VMware) or CPU Wait Time Per Dispatch (Hyper-V on Windows 2012 and later)

    These metrics indicate how long a VM has to wait for CPU time from the hypervisor. In general, this value should be < 5%.
  • Processor Queue Length

    This is the length of the queue of threads waiting for processor time. Ideally this should be low (<5), but this will depend on the server function. Create a baseline when SQL is performing well and check for deviations from this baseline value.
  • % Processor Time:

    CPU usage for the overall server - it should be <80%
  • % Privileged Time

    CPU time spent on Windows kernel commands, such as SQL Server I/O requests. If this and Physical Disk usage counters are high, this could indicate an I/O bottleneck.
  • % User Time:

    The amount of CPU spent on user processes, including SQL Server. Look for deviations from the baseline value.

Conclusion

Providing CPU resources for SQL Server is a balance between providing enough resources to ensure good performance and avoiding more license fees than are necessary by only allocating the resources that you actually need.

Want learn more?

Learn how an improved monitoring strategy can help optimize database performance and reduce the amount of dedicated resources needed for database monitoring and management.

 

Download the whitepaper:   Achieving Peak SQL Performance