How many CPUs are your SQL Server able to use?

Category: Performance
Item: CPU schedulers offline

What is a CPU Scheduler in SQL Server?

A query that you want to execute represents a task, your work. The execution context of your work is called a worker.

A task is assigned to a worker, which is assigned to a scheduler for execution on the CPU.

The manager of what gets on the CPU is called the scheduler, and there is one per logical processor.

Why should you care about it?

You have got CPUs, you pay for them, but your SQL Server may not use them all to process a workload.

Some CPU cores may not be accessible to SQL Server due to affinity masking or licensing problems.

How to check if there are CPU schedulers offline?

You can use the query below. The sys.dm_os_schedulers return 0 when the schedulers are disabled.

SELECT * FROM sys.dm_os_schedulers WHERE scheduler_id < 255
Figure 1 – CPU scheduler offline.

How to Fix Disabled Schedulers

Do you know about your license limitations?

Each edition of SQL Server has a maximum number of sockets (or physical processors or processor packages) and a maximum number of cores.

Check the capacity limits of your SQL Server edition.

If you’re using affinity masking, make sure you know what you’re doing.

Do not configure CPU affinity in the Windows operating system and also configure the affinity mask in SQL Server.

You may have unpredictable results.

SQL Server CPU affinity is best configured using the sp_configure option in SQL Server.

When using virtualization, consider increasing the number of cores per virtual socket, this can be a simple software change.

More information

Microsoft – Affinity mask Server Configuration Option.
Microsoft – Clarifying The NUMA Configuration.
Microsoft – Compute capacity limits by edition of SQL Server.

Share this post

Share on facebook
Share on google
Share on twitter
Share on linkedin
Share on pinterest
Share on print
Share on email

Leave a Comment

Your email address will not be published. Required fields are marked *

4.8/5

42

TESTIMONIALS

FREE Scripts to Instantly
Improve SQL Server Performance

3 FREE Scripts to Instantly
Improve SQL Server Performance

Learn how to increase Performance of SQL Server INSTANTLY with 3 free scripts.