SQL Server Query Optimizer Eating 100% CPU – Fix It Fast

You're Not Alone – This Is Annoying

Your SQL Server hits 100% CPU, everything slows down, and you're staring at the Query Optimizer in the process list. I've been there. Let me show you the fix.

The Immediate Fix: Kill the Runaway Query

First, you need to stop the bleeding. Run this in SSMS:

-- Find the culprit
SELECT r.session_id, r.cpu_time, r.status, t.text
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE r.cpu_time > 5000  -- 5 seconds
ORDER BY r.cpu_time DESC;

If you see a query with high CPU time and status 'runnable', note the session_id. Then kill it:

KILL [session_id];

Do this carefully — killing a transaction might roll back for a long time. But if CPU is at 100%, you don't have much choice.

Why Killing Works Here

The Query Optimizer itself isn't the problem. It's the symptom. When a query takes too long to optimize (usually because of outdated stats or a bad plan), it sits there eating CPU. Killing the query frees the CPU instantly. But the real fix comes next.

The Real Fix: Update Statistics

Most of the time, outdated statistics cause the optimizer to go crazy calculating different plans. Here's what I do first:

-- Update stats for all tables in the database
EXEC sp_updatestats;

This forces the optimizer to re-evaluate row counts and distribution. In SQL Server 2016 and older, this is almost always the fix. On 2019+ with Automatic Statistics, it's less common but still happens if you had a large data load recently.

Why Step 3 Works

The Query Optimizer uses statistics to estimate row counts. If stats are stale, it guesses wrong — sometimes by orders of magnitude. This makes it try many different join orders and index strategies, each consuming CPU. Updating stats gives it accurate numbers, so it picks a good plan quickly.

Less Common Variations

If the stats update didn't help, here's what else I've seen:

Parameter Sniffing

Sometimes a query runs fast with one parameter value but slow with another. The optimizer caches a plan based on the first value it saw. Fix it with:

-- Option 1: Add RECOMPILE to the query
SELECT * FROM Orders WHERE OrderDate > @Date OPTION (RECOMPILE);

-- Option 2: Use OPTIMIZE FOR UNKNOWN
SELECT * FROM Orders WHERE OrderDate > @Date OPTION (OPTIMIZE FOR (@Date UNKNOWN));

RECOMPILE forces a new plan every time. It adds small CPU cost but avoids bad cached plans. OPTIMIZE FOR UNKNOWN uses average values — good if you have even distribution.

Missing Indexes

The optimizer might be scanning entire tables because a key index is missing. Run this to check:

SELECT * FROM sys.dm_db_missing_index_details;

If you see rows, create the recommended indexes. This is rare but kills CPU when it happens.

Cardinality Estimation Model

SQL Server 2014 introduced a new CE model. Some queries run worse with it. If you migrated from an older version, try:

-- For the whole database
ALTER DATABASE [YourDB] SET COMPATIBILITY_LEVEL = 130;

-- Or for a specific query
SELECT * FROM Orders WHERE... OPTION (USE HINT('FORCE_LEGACY_CARDINALITY_ESTIMATION'));

I've seen this fix CPU issues on SQL Server 2017 after upgrading from 2012.

Prevention: Don't Let It Happen Again

1. Schedule stats updates – Run sp_updatestats nightly if you have big data changes.
2. Monitor with sp_WhoIsActive – Install Adam Machanic's procedure. It shows query progress, blocking, and CPU time in real time.
3. Set a query timeout – Use QUERY_GOVERNOR_COST_LIMIT or app-level timeouts to kill long-running queries automatically.
4. Check for parameter sniffing early – If you see CPU spikes around the same time each day, trace the queries and test with OPTION (RECOMPILE).
5. Keep indexes defragmented – Rebuild or reorganize when fragmentation > 30%. Fragmented indexes confuse the optimizer.

I've been running a 10 TB SQL Server 2019 instance for two years. These steps cut our CPU spikes from weekly to never. Your mileage may vary, but start with stats and killing the bad query. That fixes 80% of the cases.