SQL SERVER – Fix: SQL Server CPU Schedulers Offline

Performance troubleshooting are some of the most common things I get to see as part this blog. A number of you keep sending me interesting mails that keep me up-to-date on my learnings. Yes, it is tough to know everything, but at the same time, it is almost impossible to get all the errors one can encounter. So every mail that comes to me with strange behavior means, I get to learn something new that day. So I want to thank each one of you for giving me an opportunity to explore, learn and in the process help you.

Here is the question that came up by one of the users. They have a really a very big server – by configuration – close to 80 cores. And they said, for some strange reasons, the SQL Server was not using all of them. They felt SQL Server was not optimized for such high number of server cores. I was taken aback by this statement and immediately jumped to look at possible causes. After some through R&D I wrote back the following:

Hi DBA (masked for a reason),

You have got CPUs, but SQL Server isn’t using all of them. This can be caused by affinity masking or by SQL Server not being licensed to use all of its CPU cores.

This can be checked using a simple DMV script on sys.dm_os_schedulers looking for is_online = 0.

SELECT * FROM sys.dm_os_schedulers
GO


Disabled Schedulers Can Mean Major Performance Trouble. How would you fix Disabled Schedulers?

Here are few scenarios’ that are common and lesser known. Check for each of the following scenarios:

1. Did someone mess up affinity masking on the server? If you’re using affinity masking, make sure you know what you’re doing. You are basically restricting SQL Server’s CPU use, tying some of its hands behind its back. And this can be one of the possibilities because a junior DBA was playing around with these settings.
2. Is this a virtualized environment? Are virtual cores misconfigured by mistake? If you are using virtualization, consider increasing the number of cores per virtual socket. This is a purely software change that doesn’t usually impact performance, although there’s some gotchas around NUMA configuration. I can discuss about them some other time. But please double check.
3. Did you upgrade your SQL Server instance recently? Are you running the wrong license, or unaware of your licensing limitations? If you’re using SQL Server 2012 Enterprise, and it was upgraded from CAL-based licensing, you may be limited to 20 cores.

Post this mail, the user said they had encountered this limitation because of Point 3 mentioned above. I was pleasantly surprised and relieved because SQL Server can run with high number of cores but this misconfiguration can limit the use of cores by SQL Server. I was glad the mail helped. I just took a moment to paste the same here for everyone’s use.

Reference: Pinal Dave (https://blog.sqlauthority.com)