Last Updated on December 9, 2023 by John Morehouse
Sometimes what you have just isn’t enough to get the job done.
In certain workloads, having additional CPU might be beneficial for the immediate workload, especially when discussing SQL Server. In the old days, dynamcially adding more CPU horsepower wasn’t an option unless you magically had another physical server with more CPU readily available. With the evolvement of virtualization, this is much easier and even more easier within the cloud space. In the cloud, it’s a simple restart of the virtual machine to acquire additional compute resources, both CPU and memory.
If you happen to be running a virtual hypervisor on-premises, such as VMWare ESXi, you can configure your virtual machines to “hot add” CPU. Hot adding CPU essentially is adding additional CPU resources without having to restart the virtual machine or even shutting it down. You can add the resources in-flight and as long as the operating system and subsequent applications can handle it, there aren’t any issues. Most modern day operating systems should be able to handle this and SQL Server has been able to handle this for some time now. The change is relatively seamless to the downstream consumer.
One caveat is that the option to hot-add CPU must be either configured when the VM was initially created or you must shut the VM down to enable that feature. Once it’s enabled, you can restart the VM and then hot-add CPU whenever needed.
Let’s take a look at the steps. I have a virtual machine running on our lab environment which is running on VMWare and I am going to “hot add” CPU to the VM. It was originally running 1 socket with 4 cores and I’ll update it to run 1 socket but with 6 cores. I have also installed SQL Server 2022 Developer.
In the VM configuration, expand the CPU section. Change 4 to 6 and then click Ok. Its that simple.
Another thing to note is that you cannot change the socket count. You can only add additional cores to the existing socket. In order to change the socket count you would need to shut the virtual machine down first.
Once back into a console session, we can examine the effects (if any) of adding additional CPU for SQL Server. First, let’s check the error log.
As you can see from the log, SQL Server quickly recognized the additional 2 CPU’s and did not throw any errors. If we examine the schedulers within SQL, first we need to tell SQL Server to reconfigure so that it will recongize the new cores.
Once that is done, we can look into the DMV sys.dm_os_schedulers to see if they were added.
SELECT * FROM sys.dm_os_schedulers GO
You can see that schedulers 4 and 5 are now visible and online and were hot added. They will remain in this status until the SQL Server instance is restarted. Once restarted, they will then shift to just being visible online.
In this post I discussed how SQL Server will react to hot adding CPU to a given virtual machine. The only real caveat that I can think of is that by doing so is that you run the risk of over provisioning the hypervisor, which can cause issues long term. If you are going to enable the ability to hot add CPU, make sure you are well aware as to what other VMs are consuming to prevent CPU issues.
© 2022 – 2023, John Morehouse. All rights reserved.