The Central Processing Unit, or CPU, and often called the “brain” of a computer, is the most important part of a system. CPU speed is measured in hertz (Hz), or cycles per second. Current processor speed is measured in GHz, or billions of cycles per second.
Modern systems can have more than one CPU, and each CPU in turn can have more than one CPU core (which, in turn, might be split up into virtual cores).
For a typical SQL Server workload, single-core speed matters. It is better to have fewer cores with higher clock speeds than more cores with lower speeds, especially for non-Enterprise editions.
With systems that have more than one CPU, each CPU might be allocated its own set of memory, depending on the physical motherboard architecture.
Some CPU manufacturers have split their physical cores into virtual cores to try to eke out even more performance. They do this via a feature called simultaneous multithreading (SMT). Intel calls this Hyper-Threading, so when you buy a single Intel® Xeon® CPU with 20 physical cores, the OS will see 40 virtual cores, because of SMT.
SMT becomes especially murky with virtual machines (VMs) because the guest OS might not have any insight into the physical versus logical core configuration.
SMT should be turned on for physical database servers. For virtual environments, you need to take care to ensure that the virtual CPUs are allocated correctly.
Non-Uniform Memory Access
CPUs are the fastest component of a system, and they spend a lot of time waiting for data to come to them. In the past, all CPUs would share one bank of RAM on a motherboard, using a shared bus. This caused performance problems as more CPUs were added because only one CPU could access the RAM at a time.
Multi-Channel Memory Architecture tries to resolve this by increasing the number of channels between CPUs and RAM, to reduce contention during concurrent access.
A more practical solution is for each CPU to have its own local physical RAM, situated close to each CPU socket. This configuration is called Non-Uniform Memory Access (NUMA). The advantages are that each CPU can access its own RAM, making processing much faster. However, if a CPU needs more RAM than it has in its local set, it must request memory from one of the other CPUs in the system (called foreign memory access), which carries a performance penalty.
SQL Server is NUMA-aware. In other words, if the OS recognizes a NUMA configuration at the hardware layer, where more than one CPU is plugged in, and each CPU has its own set of physical RAM (see Figure 2), SQL Server will split its internal structures and service threads across each NUMA node.
Since SQL Server 2014 Service Pack 2, the Database Engine automatically configures NUMA nodes at an instance level, using what it calls soft-NUMA. If more than eight CPU cores are detected (including SMT cores), soft-NUMA nodes are created automatically in memory.
Figure 2-2 Two-socket NUMA configuration.
Disable power saving everywhere
Modern systems can use power saving settings to reduce the amount of electricity used by a server. Although this is good for the environment, it is bad for query performance because the CPU core speed might be reduced to save energy.
For all operating systems running MS SQL Server 2017, turn on High Performance at the OS level, and double-check that High Performance is set at the BIOS level, as well. For dedicated VM hosts, this will require downtime to make the change.