How to Diagnose SQL Server Performance Problems

SQL Server performance problems are rarely mysterious. The engine exposes a substantial amount of diagnostic information about what it is doing and what is slowing it down. The challenge is knowing where to look, in what order, and what each piece of information means for the specific situation.

This article outlines a systematic approach — starting with the broadest signals and narrowing down to specific queries and root causes.

Start with Wait Statistics

The most important question in SQL Server performance diagnosis is: what is the server waiting for? SQL Server tracks this in sys.dm_os_wait_stats. Every time a session has to pause — to read from disk, to acquire a lock, to wait for a scheduler — SQL Server records the wait type and how long it waited.

Querying this view gives an accumulated picture of where the server has spent time waiting since the last restart. Sort by wait_time_ms descending and look at the top entries. The dominant wait types tell you what category of problem you are dealing with:

• CXPACKET and CXCONSUMER: parallel query execution. Some parallelism is normal. Excessive CXPACKET waits often indicate queries running with a higher degree of parallelism than is useful, or a max degree of parallelism (MAXDOP) setting that is too high for the workload.
• LCK_M_* (e.g. LCK_M_S, LCK_M_X): lock waits. Sessions are blocked waiting to acquire locks. This points to a blocking and concurrency problem.
• PAGEIOLATCH_SH, PAGEIOLATCH_EX: I/O waits for data pages. SQL Server is reading data from disk. This could indicate insufficient memory (buffer pool too small to cache the working set), slow storage, or queries doing excessive table scans.
• SOS_SCHEDULER_YIELD: CPU pressure. Queries are competing for CPU time and yielding the scheduler. High values indicate the server is CPU-bound — too many queries, or individual queries consuming too much CPU.
• WRITELOG: transaction log write waits. Writes are being slowed by log I/O. This is often a storage latency problem on the drive hosting the transaction log.
• RESOURCE_SEMAPHORE: queries waiting for a memory grant. SQL Server has estimated that a query needs more memory than is currently available and is waiting. Often caused by bad cardinality estimates producing inflated memory grant requests.

The wait stats alone won't solve the problem, but they will reliably tell you which direction to investigate. Don't skip this step and go straight to individual queries — the wait stats give you the category of problem first.

Identify the Worst Queries

Once you have a sense of the type of problem, identify which specific queries are responsible. sys.dm_exec_query_stats contains aggregated execution statistics for all cached query plans. Join it to sys.dm_exec_sql_text to get the query text and sys.dm_exec_query_plan for the execution plan.

Sort by the metric that matches your problem:

• total_elapsed_time — total wall-clock time spent in this query across all executions. Useful for finding queries that dominate overall response time.
• total_logical_reads — total data pages read from the buffer pool. High logical reads typically mean missing indexes or table scans.
• total_worker_time — total CPU time. Useful when CPU is the bottleneck.
• total_elapsed_time / execution_count — average duration per execution. Useful when you need to find slow individual executions rather than high-volume cheap queries.

This view only contains queries whose plans are currently in the plan cache. If plans have been evicted — due to memory pressure, a server restart, or an explicit DBCC FREEPROCCACHE — historical data will be missing. This is where Query Store becomes valuable.

Use Query Store if It Is Enabled

Query Store, available since SQL Server 2016, persists query performance history across server restarts and plan cache evictions. It records execution statistics and execution plans over time, which makes it invaluable for diagnosing regressions.

The most useful capability of Query Store for performance diagnosis is plan comparison. When a query was running well last week and is slow this week, Query Store can show you whether the execution plan changed — and when. A plan change is a very common cause of sudden performance regression. Query Store also allows plan forcing: if a good plan is identified, it can be pinned so that SQL Server always uses it for that query, regardless of parameter values or statistics changes.

If Query Store is not enabled on your databases, enabling it is worth doing now, not during an incident. It has very low overhead and the diagnostic value during the next performance problem is significant.

Reading Execution Plans

For the specific queries identified as problematic, the execution plan shows exactly how SQL Server is executing the query. Plans are available in SQL Server Management Studio (SSMS) with Ctrl+M for actual plans (which include runtime row counts), or from sys.dm_exec_query_plan for cached plans.

The key things to look for:

• Estimated vs actual row counts: each operator in the plan shows estimated and actual rows. Large discrepancies (estimates of 10 when actual is 10,000) indicate stale or inadequate statistics. Wrong estimates lead to wrong plans.
• Index scans on large tables: an index scan reads all rows in an index. On a large table, this is expensive. An index seek would locate only the rows needed. Scans are often caused by missing indexes or non-sargable predicates (WHERE clauses that can't use an index efficiently, e.g. functions applied to the indexed column).
• Key lookups: when SQL Server uses a non-clustered index to find rows but then has to go to the clustered index to retrieve additional columns, it performs a key lookup. On large result sets, this is expensive. The fix is usually to add the required columns to the index using the INCLUDE clause.
• Hash joins on small datasets: hash joins are efficient for large datasets. If you see a hash join on a small table, it often means the optimizer has bad row count estimates and has chosen the wrong join strategy.
• Spill warnings: sort and hash operators that spill to disk show warnings in the plan. This means SQL Server ran out of the memory it allocated for the operation and had to use TempDB. Usually caused by under-estimated row counts.

Identify and Investigate Blocking

If wait statistics show significant LCK_M_* waits, blocking is the problem. Use sys.dm_exec_requests to see currently executing sessions: look for rows where wait_type starts with LCK_M_ and blocking_session_id is non-zero. The blocking session is the one to investigate.

For active blocking chains, sys.dm_os_waiting_tasks shows the full picture: each waiting task, what it is waiting for, and which session holds the resource. The session at the top of the chain — the head blocker — is the one that needs attention. What query is it running? How long has it been running? Is it in an open transaction?

For blocking that occurs intermittently, an Extended Events session capturing blocked process events is more effective than watching DMVs in real time.

Establish a Baseline

Performance problems are always relative to something. "The database is slow" only means something when compared against how it was running before. Without a baseline, it is difficult to quantify the problem, impossible to confirm that a fix worked, and hard to detect regressions early.

At a minimum, capture a snapshot of wait statistics, top queries by elapsed time, and key metrics (CPU, memory, I/O) from a period of normal operation. Many teams use a scheduled job that captures sys.dm_os_wait_stats snapshots every 15 minutes. This is a low-overhead practice that substantially improves the quality of future incident investigations.

The Timing Question

A question that should be asked early in any performance investigation: when did the problem start, and what changed around that time? SQL Server performance does not degrade randomly. Changes that commonly cause regressions include:

• Application or stored procedure deployments
• Large data loads or purge operations that affected statistics or fragmentation
• Index maintenance jobs that rebuilt plans or changed statistics
• SQL Server updates or configuration changes
• Changes in query patterns — new features generating new query types

If you can pin the problem to a timeframe, the number of possible causes narrows significantly.

Diagnostic Checklist

1. Query sys.dm_os_wait_stats — identify the dominant wait types
2. If I/O waits: check for table scans, missing indexes, buffer pool size
3. If lock waits: identify blocking chains, investigate head blocker
4. If CPU waits: identify top CPU-consuming queries from sys.dm_exec_query_stats
5. Retrieve execution plans for top queries — look for scan vs seek, key lookups, spills, row count discrepancies
6. Check Query Store for plan changes correlated with the regression
7. Establish when the problem started and what changed