SQL Server not using all Cores allocated to VM

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.

task manager with high CPU

One reason could be the affinity masking set to use only 4 cores in SQL Server.

affinity setting

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.

edition comparison

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:

  1. Changing the license from Standard to Enterprise edition.
  2. Changing the cores per socket configuration for the VM host for SQL Server.
core per socket setting

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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s