Troubleshooting SQL Server Performance: CPU, I/O, Blocking and Query Bottlenecks

The fastest way to diagnose SQL Server performance problems is to stop guessing and look at what the system is waiting on. Diagnosis follows four questions: Is the problem really in the database? What is SQL Server waiting on (CPU, disk, locks, memory)? Which query or session causes that wait? Did the problem start suddenly, or did it grow slowly? This post gives you a practical diagnostic flow that answers those questions in order.

The guide is written for DBAs who run SQL Server, development teams and IT managers tasked with solving “the system is slow” complaints. Every query used along the way was tested on SQL Server 2025 and also works on SQL Server 2016 and later.

The Diagnostic Flow at a Glance

1. Is the Problem Really in the Database?

A good share of “the database is slow” complaints don’t come from the database at all: network latency, a starved application server or an application that fetches data row by row produce the same symptom. The first job is to pin down the scope of the complaint:

• Is everyone slow, or one particular screen or report?
• Is it slow all the time, or at certain hours?
• When did it start, and what changed on that date?

If one screen is slow, you look for the problem at query level. If everyone slows down at the same moment, the cause is most likely resources or blocking.

2. What Is Happening Right Now?

While the problem is still going on, the first place to look is the running requests. The query below shows how long each active session has been running, what it’s waiting on and who is blocking it:

SELECT r.session_id,
       r.status,
       r.blocking_session_id,
       r.wait_type,
       r.wait_time / 1000.0 AS wait_sec,
       r.total_elapsed_time / 1000.0 AS elapsed_sec,
       r.cpu_time / 1000.0 AS cpu_sec,
       r.logical_reads,
       DB_NAME(r.database_id) AS database_name,
       SUBSTRING(t.text, r.statement_start_offset / 2 + 1,
                 (CASE r.statement_end_offset WHEN -1 THEN DATALENGTH(t.text)
                       ELSE r.statement_end_offset END - r.statement_start_offset) / 2 + 1) AS running_statement
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE r.session_id <> @@SPID
  AND r.session_id > 50
ORDER BY r.total_elapsed_time DESC;

Rows with a non-zero blocking_session_id are sessions waiting on another session. The session at the head of the chain (one that waits on nobody but makes others wait) is the source of the problem.

An example from the field: at a large manufacturer, the month-end report locked up the whole system. The session at the head of the chain was identified and killed, and the system was back to normal in 12 minutes. The report was then tuned, and its run time dropped from 4 hours to 19 minutes. The example shows two things: emergency response and the lasting fix are separate steps, and you need both.

3. What Is SQL Server Waiting On?

SQL Server records every moment a query can’t run as a wait type. Wait statistics are the compass of diagnosis: is it CPU, disk, locks or memory?

SELECT TOP (10)
       wait_type,
       wait_time_ms / 1000.0 AS wait_sec,
       signal_wait_time_ms / 1000.0 AS signal_wait_sec,
       waiting_tasks_count,
       CAST(100.0 * wait_time_ms / SUM(wait_time_ms) OVER () AS decimal(5, 1)) AS pct
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN (N'SLEEP_TASK', N'LAZYWRITER_SLEEP', N'SQLTRACE_BUFFER_FLUSH',
        N'XE_TIMER_EVENT', N'XE_DISPATCHER_WAIT', N'REQUEST_FOR_DEADLOCK_SEARCH',
        N'LOGMGR_QUEUE', N'CHECKPOINT_QUEUE', N'BROKER_TO_FLUSH', N'BROKER_TASK_STOP',
        N'DIRTY_PAGE_POLL', N'HADR_FILESTREAM_IOMGR_IOCOMPLETION', N'SP_SERVER_DIAGNOSTICS_SLEEP',
        N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP', N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG',
        N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP', N'QDS_ASYNC_QUEUE', N'SOS_WORK_DISPATCHER',
        N'FT_IFTS_SCHEDULER_IDLE_WAIT', N'XE_LIVE_TARGET_TVF', N'ONDEMAND_TASK_QUEUE',
        N'DISPATCHER_QUEUE_SEMAPHORE', N'CLR_AUTO_EVENT', N'CLR_MANUAL_EVENT', N'WAITFOR',
        N'PWAIT_EXTENSIBILITY_CLEANUP_TASK', N'PREEMPTIVE_XE_DISPATCHER', N'XE_DISPATCHER_JOIN',
        N'HADR_WORK_QUEUE', N'HADR_TIMER_TASK', N'HADR_CLUSAPI_CALL', N'HADR_NOTIFICATION_DEQUEUE',
        N'CHKPT', N'KSOURCE_WAKEUP', N'RESOURCE_QUEUE', N'SERVER_IDLE_CHECK')
  AND wait_type NOT LIKE N'SLEEP[_]%'
  AND wait_type NOT LIKE N'BROKER[_]%'
  AND waiting_tasks_count > 0
ORDER BY wait_time_ms DESC;

These values accumulate from the last server restart. When you investigate a current problem, the difference between two snapshots gives a more accurate picture.

We cover how to read each wait type and a step-by-step diagnostic flow in our SQL Server wait statistics post.

4. The Resource Bottleneck: CPU, Disk or Memory?

Disk latency

For disk-related slowness, measure latency per file instead of guessing. The query below gives the average read and write latency in milliseconds for each database file:

SELECT DB_NAME(vfs.database_id) AS database_name,
       mf.physical_name,
       CAST(vfs.io_stall_read_ms / NULLIF(vfs.num_of_reads, 0) AS decimal(10, 1)) AS avg_read_ms,
       CAST(vfs.io_stall_write_ms / NULLIF(vfs.num_of_writes, 0) AS decimal(10, 1)) AS avg_write_ms,
       vfs.num_of_reads,
       vfs.num_of_writes
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS vfs
JOIN sys.master_files AS mf
  ON mf.database_id = vfs.database_id AND mf.file_id = vfs.file_id
ORDER BY vfs.io_stall_read_ms + vfs.io_stall_write_ms DESC;

On shared storage and virtualized platforms, taking the problem to the virtualization or storage team with measurements like these (IOPS, latency, queue length) speeds up the resolution considerably. You’ll find the ground rules for disk configuration in our disk storage guide.

CPU

Under CPU pressure, first check whether SQL Server can actually use all the cores; core limits caused by licensing or virtualization are a common culprit. We explain the details in how many CPUs SQL Server can use. Then move on to the top CPU queries (step 5).

Memory

PAGEIOLATCH waits and constant reads from disk can point to a memory shortage. More often, though, the real problem isn’t memory but queries that read far more data than they need. Look at the queries with the most reads before you add memory.

5. Find the Responsible Query

If Query Store is on, you can see after the fact when a query got slower and which execution plan it used. If Query Store is off, start with the statistics in the plan cache:

SELECT TOP (10)
       qs.total_worker_time / 1000 AS total_cpu_ms,
       qs.total_logical_reads,
       qs.execution_count,
       qs.total_worker_time / qs.execution_count / 1000 AS avg_cpu_ms,
       SUBSTRING(t.text, qs.statement_start_offset / 2 + 1,
                 (CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(t.text)
                       ELSE qs.statement_end_offset END - qs.statement_start_offset) / 2 + 1) AS statement_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS t
ORDER BY qs.total_worker_time DESC;

Change the sort to total_logical_reads to see the queries that read the most. Reads are the most reliable measure in query tuning; we explain why in why reads matter when tuning T-SQL.

6. What Changed?

If a system that used to be fast has suddenly slowed down, something has almost always changed:

Common Diagnostic Mistakes

• Adding hardware first: an inefficient query is just as inefficient with twice the CPU; the problem is only postponed.
• Clearing the plan cache: DBCC FREEPROCCACHE may bring brief relief, but it forces every plan to recompile and wipes out the evidence.
• Everyone stepping in at once: when VM settings, session kills and setting changes all happen together, nobody can tell which one helped.
• Restarting the server: it hides the problem for a while and resets wait statistics and caches; without the root cause, the problem comes back.

We’ve collected other common tuning mistakes in our 12 mistakes post.

SQL Server Performance Tuning with Aryasoft

When the Aryasoft team responds to a performance problem, it first looks at monitoring alerts, the error log, active sessions, Agent jobs, resource usage, disk latency and how CPU is spread across logical processors. We show the bottleneck with evidence, bring the emergency under control and then apply the lasting fix. For real examples, see the 100% CPU case at a public institution and reducing SQL Server CPU usage with partition-aware indexing. Service details are on our SQL Server consulting page.

Frequently Asked Questions

Sources

The technical details in this guide were checked against the following official documentation in November 2026.

1. Microsoft Learn: sys.dm_exec_requests
2. Microsoft Learn: sys.dm_os_wait_stats
3. Microsoft Learn: sys.dm_io_virtual_file_stats
4. Microsoft Learn: sys.dm_exec_query_stats
5. Microsoft Learn: Monitor performance by using the Query Store
6. Microsoft Learn: Troubleshoot slow-running queries