The Two Settings That Fixed Our Production CPU Crisis

How default SQL Server configuration brought a production server to its knees — and how two settings fixed it in under a minute.

The Escalation

It started with a message from my manager:

Attached was a screenshot of CPU pinned near 100%. Not a good look on a production server handling live traffic.

The Investigation

My first instinct was to hunt for a rogue query. That's usually the culprit — some poorly optimized SELECT statement doing a full table scan, or a report running during peak hours. I pulled up the monitoring dashboards and started digging through the active sessions during every CPU spike.

Nothing. No single query was eating the CPU. No runaway process. No obvious offender.

So I took a step back and did what I should have done first: check the SQL Server configuration itself.

The Root Cause

Two settings. That's all it was.

The server was running with default values for two critical performance settings:

Setting	Default Value	The Problem
max degree of parallelism (MAXDOP)	0 (unlimited)	Every query could grab all available cores. Dozens of queries fighting over the same CPUs simultaneously.
cost threshold for parallelism	5	Ridiculously low. Even trivial queries were being parallelized when they had no business being parallelized.

Here's why this combination is so damaging: with MAXDOP at 0, SQL Server says "use as many cores as you want" for every parallel query. And with the cost threshold at 5, almost every query qualifies for parallelism. The result? The CPU wasn't doing useful work — it was managing a traffic jam. Threads waiting on other threads, cores context-switching instead of executing. The telltale sign: CXPACKET waits through the roof.

The CPU wasn't overloaded by a heavy workload. It was overloaded by its own parallelism overhead.

The Fix

Two commands. No restart required. No downtime. No code change.

-- Enable advanced options
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;

-- Limit each query to 4 cores max
EXEC sp_configure 'max degree of parallelism', 4;

-- Only parallelize queries that are actually heavy
EXEC sp_configure 'cost threshold for parallelism', 50;
RECONFIGURE;

Before: CPU regularly spiking to 90-100%, causing visible latency on live traffic.

After: CPU settled down. Queries stopped fighting each other. The server could breathe again.

My manager followed up: "Have you verified all 8 production servers that no other server has a similar config problem?"

I checked every one. Only this server was running with defaults. The rest had been configured properly during their initial setup. This one slipped through the cracks.

Why Defaults Are Dangerous

SQL Server's default configuration is designed to work, not to perform. It ships with settings that are safe for a fresh install on unknown hardware. But in production, those defaults can actively work against you.

I've heard a story that perfectly illustrates this. A company spent huge amount on new hardware trying to fix slow application response times — more memory, bigger disks, more cores. Nothing worked. Response times kept climbing from under a second to 10-15 seconds over three months.

The root cause? Their brand new 128-core machine had MAXDOP at 0. Every query was free to grab all 128 cores. The new hardware they bought to fix the problem actually made it worse. More cores meant more contention. More contention meant slower queries. Every money spent made things worse.

Two settings changed. CPU dropped from 95% to 30%. Response times fell from 10 seconds to under one.

The lesson is the same one I learned on our production server: defaults are starting points, not best practices.

Understanding the Settings

MAXDOP (Max Degree of Parallelism)

Controls how many CPU cores a single query can use when it goes parallel.

• Default (0) = "Use all available cores." Sounds great in theory. In practice, it means dozens of queries each trying to claim every core on the box, spending more time coordinating than actually working.

• Recommended = Set it based on your core count and NUMA configuration. Common values are 4 or 8 for most OLTP workloads.

  
  

Cost Threshold for Parallelism

The minimum estimated cost a query must have before SQL Server considers running it in parallel.

• Default (5) = Absurdly low. This value has been the default since the 1990s and has never been updated. Almost any non-trivial query exceeds a cost of 5, which means nearly everything gets parallelized.

• Recommended = 50 is a solid starting point. This means only genuinely heavy queries get parallel execution plans. The lightweight ones run on a single thread, which is faster for them anyway.

  
  

What Are CXPACKET Waits?

When you see high CXPACKET waits in your wait statistics, it means parallel threads are waiting on each other. It's the symptom of over-parallelization — too many queries going parallel, too many threads per query, or both. Tuning MAXDOP and Cost Threshold directly reduces these waits.

Using dbatools for Baseline Configuration

Before making changes, I used dbatools — the community-maintained PowerShell module for SQL Server — to check the baseline configuration across all servers. This is the fastest way to audit and remediate configuration drift across multiple instances:

# Check and set recommended max memory allocation
Test-DbaMaxMemory -SqlInstance localhost | Set-DbaMaxMemory

# Check and set recommended MAXDOP based on server hardware
Test-DbaMaxDop -SqlInstance localhost | Set-DbaMaxDop

# Set Cost Threshold for Parallelism to 50
Set-DbaSpConfigure -SqlInstance localhost `
    -Name CostThresholdForParallelism -Value 50 | Format-Table

What makes dbatools powerful here is that Test-DbaMaxDop doesn't just blindly set a number — it analyzes your server's core count and NUMA topology and recommends the right MAXDOP value for your specific hardware. No guesswork.

If you're managing more than one SQL Server instance, you can pipe an array of server names through these commands and audit your entire fleet in seconds.

The Takeaway

If your SQL Server has been running with default MAXDOP and Cost Threshold values in production, you're likely leaving significant performance on the table — or worse, actively degrading it.

Here's a quick self-check:

-- Check your current settings
EXEC sp_configure 'max degree of parallelism';
EXEC sp_configure 'cost threshold for parallelism';

If MAXDOP returns 0 and Cost Threshold returns 5 on a production server with dozens of cores — that's a conversation worth having today. Don't wait for the Friday evening escalation.

Disclaimer: Don't blindly copy the values from this post. MAXDOP 4 worked for our server and workload. Your number might be 2, 8, or 16 depending on your core count, NUMA configuration, and workload patterns. Use Test-DbaMaxDop to get a hardware-specific recommendation, test in a non-production environment first, and monitor after applying changes. Defaults are bad, but so is cargo-culting someone else's settings.

Further Reading:

• Troubleshooting the CXPACKET Wait Type in SQL Server — SQLShack

• dbatools — Community PowerShell module for SQL Server

• Configure MAXDOP — Microsoft Learn