sp_Blitz® Result: Disabled Schedulers
You’ve 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 part of our SQL Server sp_Blitz® script checks sys.dm_os_schedulers looking for is_online = 0:
SELECT * FROM sys.dm_os_schedulers WHERE is_online = 0;
Disabled Schedulers Can Mean Major Performance Trouble
To learn more, read Brent’s post, “Why Core-Based Licensing Matters for Performance Tuning.”
Just Disabling a CPU Doesn’t Mean You Don’t Need to License It
Some bad news here- with the exception of a very limited scenario allowing customers with Software Assurance to upgrade to SQL Server 2012, you are required to license all the cores in a SQL Server, even if you aren’t using them. Just setting up affinity masking does not mean you don’t need to pay for those cores.
To Fix the Problem
With all of the possible underlying causes, you’re going to want to tread carefully here. Research what’s causing the issue using the information below and test and evaluate the solution that’s right for you.
How to Fix Disabled Schedulers
Check for each of the following scenarios:
Did someone mess up affinity masking? If you’re using affinity masking, make sure you know what you’re doing. You’re basically restricting SQL Server’s CPU use, tying some of its hands behind its back.
Are virtual cores misconfigured? If you’re 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.
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.