Is the CPU Important for SQL Server?

Introduction

My question here is the CPU important for SQL Server?

I had a conversation with a very experienced and capable DBA recently. As part of this conversation, they showed me a detailed architectural diagram that showed their overall physical architecture. This included the number of servers (which was quite low) and where they were located across two data centers.

Curious, I asked the DBA what make, and model CPUs were being used in these database servers. I was impressed with how well their workload ran on their limited infrastructure.

The immediate answer about the CPU make and model was “I don’t know”, which surprised me. I guess I should not have been surprised, since this is a very common answer from DBAs that I talk to. This answer always puzzles me though. It was rant #1 in my recent post T-SQL Tuesday #152 – Database Rants.

Within a few seconds, this DBA found the information and gave me the answer. It was an Intel Xeon E5-2697A V4 “Broadwell-EP” processor, which was a flagship SKU when it was released in Q1 2016.

Is the CPU Important for SQL Server?

Obviously, I think so. The CPU(s) that you are using in a SQL Server instance have a direct effect on your CPU capacity and performance (which are different things). It also affects your SQL Server licensing costs. Faster CPUs can often let you have fewer cores for the same workload, which can dramatically reduce your license costs.

In addition, it affects many other things for your server. This includes:

• The amount and type of RAM you can use
  • This could be DDR3, DDR4 or DDR5 (in the near future)
• Limits on your RAM bandwidth
  • The memory controller is on the CPU
  • Some Intel CPUs limit memory bandwidths with less expensive SKUs
• What PCIe version you can use
  • This could be PCIe 3.0, 4.0 or 5.0 (in the near future)
• Which CPU instructions are supported
  • This first became important with SQL Server 2016
  • It will become even more important with SQL Server 2022
• Whether there are any specialized hardware off-loading components on the CPU
  • This will be important with SQL Server 2022

Here is an example. SQL Server 2022 CTP 2.1 leverages new hardware capabilities – including the Advanced Vector Extension (AVX) 512 extension to improve batch mode operations in queries. Whether or not you have AVX-512 support depends on your processor. Upcoming AMD Genoa and Intel Sapphire Rapids server processors will have AVX-512 support. Current Intel Ice Lake server processors have AVX-512 support.

I’m a DBA, not a Hardware Geek

Even if you could care less about hardware and CPU selection, it still affects you as a DBA, whether you like it or not. Daily DBA tasks such as backup/restore, running DBCC CHECKDB, doing index tuning, and just keeping the server running well overall are directly affected by the CPU(s) that the server is running on.

The single-threaded CPU performance of your processor is usually the final performance bottleneck in a well-tuned system that does not have any other substantial bottlenecks. This means that a faster CPU (from the same product family) will make many of your queries simply run faster than a slower CPU from that family. This difference can sometimes be quite significant.

A new generation CPU, perhaps from a different CPU vendor, can make an even more substantial difference!

Over my career, nobody has ever complained to me that their database server (or cloud “instance”) was too fast. Organizations are quick to complain (and blame the default blame acceptor) when the application is slow.

No matter how amazing your developers are or how skilled you are as a workload and query tuner, a faster processor is always going to have beneficial effects on performance. On the other hand, even the finest hardware and storage can be overwhelmed by poorly designed applications and databases.

What if I am Running in the Cloud?

It doesn’t matter. Cloud “instances” of SQL Server, whether they are IaaS or PaaS still ultimately exist on physical servers in the cloud provider’s data center. You are usually running in a hypervisor, but the hypervisor is running on a physical server, where the component choices still matter.

The CPU choices the cloud provider makes and the choices they offer are even more important than they were on-premises. A bad choice here can have many negative consequences, including higher monthly operational costs, lower performance and less capacity.

To be sure, many cloud providers are intentionally and frustratingly vague about the exact CPU model and specifications in their various cloud offerings. All of the large cloud providers also like to use custom SKUs for AMD or Intel processors. These custom SKUs will have different specifications than the “regular” SKUs that server vendors sell. This makes it harder to figure what is a good choice.

Here is an example, from Amazon. This is actually a very good AWS EC2 instance type for SQL Server, but Amazon is very vague about the CPU specifications.

They only reveal that the processor is a 3rd Generation Intel Scalable “Ice Lake” with an all-core turbo frequency of 3.5 GHz. Nothing about the number of cores, base clock speed, L3 cache size or TDP rating.

Intel Ice Lake server processors were released in Q2 2021, and they can have up to 40 physical cores. They have DDR4-3200 RAM, PCIe 4.0 support and AVX-512 support. These are pretty decent processors for SQL Server, depending on which SKU is used. And that is the rub. Not all Ice Lake SKUs are “good” choices for SQL Server, because of their various specifications.

How Can I Learn More?

One thing you can do is to come to the PASS 2022 Data Community Summit in Seattle in November. I will be presenting Selecting and Sizing Cloud VMs for SQL Server on Monday, November 14th. I would be honored if you decided to attend my session.

Registering for the PASS Data Community Summit 2022 and a pre-con is very easy. Simply visit the signup page, register, and select the pre-con that you want to attend.

Final Words

My premise is that a Production DBA should know what processors are used by their most mission critical database servers. Once you know the make and model number of the CPU, you can use your favorite search engine to get the detailed specifications (including the age) of that processor. If you don’t know what you have, you should find out this information.

Even if you think you have no control over your current hardware choices and no realistic hope of making a change, it is still better to know what you have to work with. Knowing this also helps you build a case for a possible platform upgrade.

Nobody has ever come to me and said, “Hey Glenn, would you like to have a new database server?”. I have also never had anyone ask me if I would like to upgrade to the latest version of SQL Server. It has always been up to me to build the technical and business case for a hardware or version upgrade.

To be clear, this is not about upgrading to the latest cool thing just because it is new and shiny. This is not the latest iPhone. Rather, it is about using your knowledge and experience to solve or mitigate problems that affect your organization (and make your job more difficult).

If you have any questions about this post, please ask me here in the comments or on Twitter. I am pretty active on Twitter as GlennAlanBerry. Thanks for reading!