How much CPU does Microsoft’s SQL Server use?

February 15, 2021 | Ken Leoni

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.
   

   Download Achieving Peak SQL Performance  –  Learn how an improved monitoring strategy can help optimize database performance and reduce the amount of dedicated resources needed for database monitoring and management

Licensing: How many cores can you use?

Microsoft SQL Server 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	Standard Per core	Standard + CAL	Web	Express
Maximum number of cores	Operating system maximum	Lesser of 4 sockets or 24 cores	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 re-seller 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. For containers, each v-core (or virtual processor, virtual CPU, virtual thread) allocated to the container must be licensed, with a minimum of 4 license units.

   If you are operating a SQL Server 2019 Big Data Clusters, you will need to purchase a SQL Server Per Core license for your Master Instance, you’ll also need to purchase Software Assurance or a subscription as well. 

   Keep in mind with Software Assurance Microsoft includes a limited number of Big Data node core licenses. For example, if you purchase a 4 core Enterprise Edition license for your Master Instance, you are entitled to 8 times the 4 cores, or 32 cores for your Big Data nodes.

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 Server 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.

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 starting with SQL Server 2014 SP2. 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.

Starting with SQL Server 2016 (13.x), if the Database Engine detects more than eight physical cores per NUMA node or socket at startup, soft-NUMA nodes are created automatically by default.

NUMA	Logical Processor Count	SQL Server 2008 through SQL Server 2014 (12.x),	SQL Server 2016 (13.x) +
Server with single NUMA node	<= 8	Set MAXDOP at or below # of logical processors	Set MAXDOP at or below # of logical processors
Server with single NUMA node	> 8	Set MAXDOP to 8	Set MAXDOP to 8
Server with multiple NUMA nodes	<=8 per NUMA node	Set MAXDOP at or below # of logical processors per NUMA node
Server with multiple NUMA nodes	>8 per NUMA node	Set MAXDOP to 8
Server with multiple NUMA nodes	<=16 per NUMA node		Set MAXDOP at or below # of logical processors per NUMA node
Server with multiple NUMA nodes	>16 per NUMA node		Set MAXDOP at half the number of logical processors per NUMA node with a MAX value of 16

SQL Server 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 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.

 

 

Editor’s Note:  This post was originally published in July 2017 and has been updated for freshness accuracy, and comprehensiveness.