Recently in one of my project we were having high CPU utilization, reaching 100% utilization for 4 cores, and it was decided to bump up the number of cores by another 4.
After rebooting the VM we were still getting the high CPU utilization alerts from SCOM on checking the server found out that SQL Server was not using all the cores.
One reason could be the affinity masking set to use only 4 cores in SQL Server.
It was set to using the maximum number of cores SQL Server “can” use. We are using can because the number of cores SQL Server will use depends upon it’s edition.
The SQL Server edition was indeed Standard, and as VM was using 1 core per socket it was just using 4 cores. We can check the core usage from the sys.dm_os_schedulers dmv.
We can see that 4 cores are in “VISIBLE OFFLINE” state.
Their are two solutions for making SQL Server use all the available cores:
- Changing the license from Standard to Enterprise edition.
- Changing the cores per socket configuration for the VM host for SQL Server.
Important thing to note, SQL Server Standard edition is core based license only so we cant just add another core without having the license for it.
being a dba 