SQL Server sucking up CPU?

Complete diagnostic and fix guide — the 6 most common causes, the DMV queries, and how to fix each one.

CPU pinned at 100% on SQL Server almost never has a single cause — and "restart the service" is never a fix, just a delay of the next incident. This guide covers the most common causes, the diagnostic queries a senior DBA runs first, and how to fix each one.

Where to start: isolate the cause before acting

The most common mistake under pressure is touching configuration before knowing what's actually consuming CPU. Three questions need answers before any adjustment:

1. Is it one or a few specific queries consuming CPU, or is it distributed across many sessions?
2. Is it a one-off spike (maintenance job, ETL, backup) or a sustained pattern throughout the day?
3. Is the high CPU coming from sqlservr.exe, or from another process on the same server (antivirus, third-party backup software, another service)?

The query below already answers the first question — it identifies the queries that have consumed the most CPU since the last service restart (or since the last plan cache flush):

SELECT TOP 20
    qs.total_worker_time / 1000 AS total_cpu_ms,
    qs.execution_count,
    (qs.total_worker_time / qs.execution_count) / 1000 AS avg_cpu_ms,
    qs.total_elapsed_time / 1000 AS total_duration_ms,
    SUBSTRING(qt.text, (qs.statement_start_offset/2) + 1,
        ((CASE qs.statement_end_offset
            WHEN -1 THEN DATALENGTH(qt.text)
            ELSE qs.statement_end_offset END
            - qs.statement_start_offset)/2) + 1) AS query_text,
    DB_NAME(qt.dbid) AS database_name
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
ORDER BY qs.total_worker_time DESC;

total_worker_time is the column that measures actual CPU time consumed — not total_elapsed_time, which measures total duration including I/O wait and blocking. Confusing the two is a common diagnostic mistake.

The 6 most common causes

1. Outdated statistics driving bad execution plans

SQL Server's query optimizer picks an execution plan based on cardinality estimates, which come from table statistics. Outdated statistics — common after large data loads, bulk DELETEs, or AUTO_UPDATE_STATISTICS disabled — lead the optimizer to pick inefficient plans, frequently trading a seek for a full scan.

-- Check when statistics were last updated
SELECT
    OBJECT_NAME(s.object_id) AS table_name,
    s.name AS stats_name,
    STATS_DATE(s.object_id, s.stats_id) AS last_updated,
    sp.rows,
    sp.modification_counter
FROM sys.stats s
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) sp
WHERE OBJECT_NAME(s.object_id) NOT LIKE 'sys%'
ORDER BY sp.modification_counter DESC;

2. Parameter sniffing — the right plan for the wrong parameter

SQL Server compiles and caches the execution plan from the first call of a parameterized stored procedure. If that first call used an atypical value (say, a customer with order volume far above average), the plan stays optimized for that one case — and degraded for everyone else. It's the classic cause of "this procedure runs fast sometimes and slow other times, with the same data".

-- Force recompilation on every execution (diagnostic use only)
EXEC dbo.MyProcedure @Parameter = 123 WITH RECOMPILE;

-- Or, in the procedure definition, for recurring cases:
-- OPTION (OPTIMIZE FOR (@Parameter UNKNOWN))

3. MAXDOP and Cost Threshold for Parallelism outside the recommended range

The default MAXDOP = 0 lets SQL Server use every available core for a single query — which can cause severe contention on multi-core servers under high concurrency, visible as CXPACKET/CXCONSUMER waits. Microsoft recommends specific MAXDOP values based on core count and NUMA node topology — almost never the default.

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

-- Most common waits associated with poorly tuned parallelism
SELECT wait_type, wait_time_ms, waiting_tasks_count
FROM sys.dm_os_wait_stats
WHERE wait_type IN ('CXPACKET', 'CXCONSUMER')
ORDER BY wait_time_ms DESC;

4. Tempdb contention consuming CPU and generating waits

Environments with a single tempdb data file suffer page contention on internal structures (PFS, GAM, SGAM) under high concurrency — every session fighting over the same page generates extra CPU overhead on top of the wait itself. Microsoft's standard recommendation is multiple equally-sized data files, typically one per logical core up to a limit of 8.

-- Check current tempdb data file count
SELECT name, physical_name, size/128 AS size_mb
FROM tempdb.sys.database_files
WHERE type = 0;

5. Non-parameterized ad hoc queries bloating the plan cache

Applications generating dynamic SQL without parameterization (concatenating values directly into the query string) force SQL Server to compile a new plan for every variation — burning CPU on compilation that should be spent on execution. Database-level Forced Parameterization can mitigate this without a code change, but fixing it in the application is the real solution.

-- Compilation and recompilation rate (perfmon counters via DMV)
SELECT cntr_value AS batch_requests_sec
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Batch Requests/sec';

SELECT cntr_value AS sql_compilations_sec
FROM sys.dm_os_performance_counters
WHERE counter_name = 'SQL Compilations/sec';

6. Missing or fragmented indexes forcing full scans

A missing index forces the optimizer to scan the entire table to find the needed rows — CPU work proportional to table size, not result size. Existing but heavily fragmented indexes have a similar, smaller-scale effect.

-- Missing index suggestions identified by the optimizer itself
SELECT
    migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) AS improvement_measure,
    mid.statement AS table_name,
    mid.equality_columns,
    mid.inequality_columns,
    mid.included_columns
FROM sys.dm_db_missing_index_group_stats migs
JOIN sys.dm_db_missing_index_groups mig ON migs.group_handle = mig.index_group_handle
JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
ORDER BY improvement_measure DESC;

Before you start changing configuration

• Never leave a permanent WITH RECOMPILE in production as a fix — it's a diagnostic tool, not a solution. Recompiling on every execution has its own CPU cost.
• Don't change server-wide MAXDOP without measuring the impact — the right value depends on workload pattern (OLTP vs. data warehouse) as much as on hardware.
• Confirm the high CPU is actually coming from sqlservr.exe before making any adjustment — we've seen cases where "SQL Server eating CPU" was actually antivirus software scanning the .mdf/.ldf files in real time.

Need help diagnosing your environment right now?

If the server has sustained CPU at a critical level, don't wait for a full Health Check — talk to our emergency support team.

Article from the SQL Server Consulting technical cluster by HTI Tecnologia — 35+ years sustaining critical databases in Brazil.