What Causes High CPU Usage in SQL Server and How to Fix It?

 

High CPU spikes, often from unoptimized queries or parallelism, are a common search as they cause app slowdowns in multi-user environments.

Scenario: A financial app's reporting module pegs CPU at 90% during end-of-day processing, delaying transactions.

Detailed Solution with Code Examples:

• Step 1: Monitor CPU. Use Performance Monitor (PerfMon) or DMVs:
  sql

  SELECT

  scheduler_id,

  current_tasks_count,

  runnable_tasks_count,

  current_workers_count,

  active_workers_count,

  work_queue_count,

  pending_disk_io_count

  FROM sys.dm_os_schedulers

  WHERE scheduler_id < 255;
  If runnable_tasks_count >0, there's CPU pressure.
• Step 2: Find Top CPU-Consuming Queries.
  sql

  SELECT TOP 10

  qs.total_worker_time / qs.execution_count AS avg_worker_time,

  qs.execution_count,

  qt.text 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;
• Step 3: Address Parameter Sniffing or Bad Plans. Add OPTION (RECOMPILE):
  sql

  SELECT * FROM YourTable WHERE Column = @Param OPTION (RECOMPILE);
• Step 4: Tune Parallelism. Check and adjust MAXDOP:
  sql

  EXEC sp_configure 'show advanced options', 1;

  RECONFIGURE;

  EXEC sp_configure 'max degree of parallelism', 4; -- Set to number of cores/2 for OLTP

  RECONFIGURE;
• Step 5: Clear Plan Cache Selectively.
  sql

  DECLARE @PlanHandle VARBINARY(64);

  SELECT @PlanHandle = qs.plan_handle

  FROM sys.dm_exec_query_stats qs

  CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt

  WHERE qt.text LIKE '%YourQuery%';

  DBCC FREEPROCCACHE(@PlanHandle);

Best Practices:

• Set cost threshold for parallelism to 50+ to avoid parallel plans for simple queries.
• Use Resource Governor to limit CPU for specific workloads.
• Update to SQL Server 2025 for better adaptive query processing.
• Monitor signal waits; if >25% of total waits, it's CPU-bound.
• In cloud environments like Azure SQL, scale up vCores temporarily.

Fixing this prevents outages, keeping CPU under 70% during peaks.

📘Free Job Preparation Zone 🎯 Visit Free Learning Zone