Not Using All Cores

This is just one of the many checks that our Daily Checkup and Quickscan Report from Stedman Solutions will report on.

Understanding SQL Server CPU Utilization

When working with SQL Server, you might encounter situations where it seems not to utilize all available cores or is capped at a maximum of 50% CPU load. There are several reasons why this might happen. Here’s an in-depth look into common scenarios and settings that can lead to such behavior:

1. Max Degree of Parallelism (MAXDOP)

SQL Server uses the MAXDOP setting to control the number of processors used for the execution of a query in a parallel plan. Setting this too low can prevent SQL Server from utilizing all available cores. It’s crucial to configure this appropriately based on your system’s configuration and workload.

2. Resource Governor

The Resource Governor is a feature in SQL Server that allows you to manage workloads and resources by specifying limits on resource consumption. If configured to limit CPU usage, it might prevent SQL Server from using full CPU capacity.

3. Affinity Mask

The affinity mask option binds SQL Server to a specified subset of CPUs. Incorrect configuration might restrict SQL Server to fewer cores than are available, leading to underutilization.

4. Virtualization

If SQL Server is running on a virtual machine, the host’s configuration can limit the number of cores assigned. Additionally, other VMs on the same host might be consuming resources, affecting your SQL Server instance’s performance.

5. Licensing Limitations

Certain editions of SQL Server have core usage limitations. Ensure your edition supports full utilization of your hardware.

6. Non-parallel Queries

Some queries cannot be parallelized due to their nature or settings like MAXDOP 1. These will not utilize all cores as they are designed to run on a single thread.

7. CPU Throttling

On some systems, especially in virtual environments, CPU resources can be throttled to prevent overheating or based on system policy, limiting the CPU available to SQL Server.

8. Wait Statistics

High waits or blocking can cause SQL Server to appear less busy than it is. This doesn’t necessarily mean it isn’t trying to use all the cores, but rather that something is preventing it from doing so.

To diagnose why SQL Server isn’t utilizing all cores or is capped at 50% CPU, look at the system as a whole, including SQL Server configuration, hardware, virtualization settings, and the nature of the workload. Tools like Database Health Monitor can be invaluable in this situation. It provides insights into server health, performance, and can help identify bottlenecks or misconfigurations affecting CPU usage.

If you’re looking to improve your SQL Server performance and want a more in-depth understanding, consider enrolling in Stedman’s SQL School classes.

Posted by: Steve Stedman