As a SQL Server DBA, one of the most common issues I come across is performance troubleshooting. It’s always interesting to receive emails from readers sharing their experiences and challenges, as it gives me an opportunity to learn and explore new concepts. Recently, I received a question from a user who had a large server with close to 80 cores, but noticed that SQL Server was not utilizing all of them efficiently. This raised concerns about whether SQL Server is optimized for high core counts.

Upon investigating the issue, I discovered a few possible causes for SQL Server not utilizing all the available cores:

Affinity Masking

One potential cause is affinity masking, which restricts SQL Server’s CPU usage by tying its hands behind its back. If someone has mistakenly configured affinity masking, it can limit the number of cores SQL Server can utilize. To check if affinity masking is enabled, you can use the following DMV script on sys.dm_os_schedulers:

SELECT * FROM sys.dm_os_schedulers
GO

If you find any schedulers with the “is_online” column set to 0, it indicates that those schedulers are disabled and can cause performance issues.

Virtualization Misconfiguration

If your SQL Server is running in a virtualized environment, it’s important to ensure that the virtual cores are properly configured. Sometimes, misconfigurations can occur, leading to suboptimal performance. Consider increasing the number of cores per virtual socket, as this is a software change that usually doesn’t impact performance. However, be cautious of any NUMA configuration gotchas that may arise.

Licensing Limitations

Another factor to consider is licensing limitations. If you recently upgraded your SQL Server instance, it’s possible that you may be running the wrong license or be unaware of your licensing limitations. For example, if you upgraded from CAL-based licensing to SQL Server 2012 Enterprise, you may be limited to a maximum of 20 cores. It’s crucial to verify your licensing details to ensure you are not inadvertently limiting the use of cores by SQL Server.

By addressing these potential causes, you can optimize SQL Server’s performance and ensure it effectively utilizes all available cores. It’s important to regularly monitor and review your server’s configuration to identify any performance bottlenecks and make necessary adjustments.

I hope this information helps you in troubleshooting and optimizing your SQL Server performance. If you have any further questions or concerns, feel free to reach out to me. Happy optimizing!