SQL Server Performance: How to Improve It and Find the Causes

To improve SQL Server performance, you first need a narrowed-down finding: Which task is too slow, when does it happen, and what distinguishes the fast state from the slow one? Execution plans, parameters and wait statistics help answer these questions.

This guide walks you through the first diagnostic steps. You choose the diagnostic path that matches your observation, compare measurements, put them in context and justify the next test. The detailed paths include read-only SQL queries you can copy, tested on SQL Server 2017 through 2025. You do not need any PSG tool for this.

What exactly is slow on your SQL Server?

Start by describing one specific task, such as an order list, a search or a nightly report. "The server is slow" is understandable, but it covers many possible causes.

These three questions help narrow it down:

Also record where you measure the time. The time a person spends waiting in front of an application covers more than the execution of a single SQL statement. It can include several database calls, the transfer of the results and the processing in the application. If the query finishes quickly in SQL Server but the view keeps loading, the application path is part of the investigation as well.

Choose the right diagnostic path

Find the row that best describes your observation. The paths overlap: a plan change, for example, can cause additional locks. Where that happens, the paths refer to each other.

Paths 02 to 04 have their own detailed pages with SQL queries. Paths 01, 05 and 06 are covered right here in short form, with links to related articles.

About to restart? Save a snapshot first. Why does restarting SQL Server help, and why only briefly? shows what a restart wipes out and how to save the evidence in five minutes.

One query is always slow

Where does this query lose its time? These five steps narrow it down:

1. Find the right query. Map the slow application step to the statement that is actually executed. If a procedure calls further statements, triggers or functions, their time is part of the outer call.
2. Rule out blocking. If the query runs long because it is waiting for a lock, tuning will not help. During a slow run, check this with the blocking chain query.
3. Measure the work. On a test system with comparable data, SET STATISTICS IO ON and SET STATISTICS TIME ON show the pages read per table and the CPU time. Note that this runs the query again, with all its cost and, for statements that modify data, with their changes.
4. Read the plan. In SSMS, turn on Include Actual Execution Plan (Ctrl+M). Look for large differences between estimated and actual rows, for a very large number of lookups, and for warning icons, for example when a sort does not fit into its granted memory and spills to tempdb. Not every warning is automatically the cause.
5. Choose one limited test: a different access path or index, a changed query shape, or a better estimate. Then measure again with the same data.

Going deeper: Parameter Sniffing in SQL Server Explained covers estimation, compilation and plan reuse. If your query has many optional search conditions, Dynamic SQL for Catch-All Procedures: T-Lift shows one possible approach.

The slowdown is over and the evidence is missing

What can you still find out now, and what should you capture next time? A past slowdown rarely leaves no traces at all.

What is often still available:

• Query Store, if it was enabled for the database: runtimes and plans per time window. The query "Most expensive queries in a time window" shows them for the period in question, including aborted executions.
• The system_health session, which runs by default: among other things, it records deadlocks and sessions that waited longer than 30 seconds for a lock. How far back it reaches depends on how much has been recorded since. Microsoft describes what system_health captures.
• The SQL Server error log, for example with messages about very slow I/O requests (message 833).
• Monitoring and application logs, if available.

What is gone: the blocking chain at the time, the requests that were running then, and their waits. The plan in the cache today is also not necessarily the plan of the slow execution. sys.dm_os_wait_stats counts since the instance started and has no notion of a time window.

If the server has been restarted in the meantime, even more is missing: the plan cache, execution statistics, and wait statistics start again at zero. If a restart is coming up, save the snapshot first; Why does restarting SQL Server help, and why only briefly? shows how to do this in five minutes and why a restart often helps only briefly.

For next time:

1. Record the time, the affected task and the time basis of your measurement.
2. Enable Query Store for the affected database if it is missing. This is a change to the database; coordinate it with operations.
3. Keep the blocking chain query and the query for waits in the time window ready so you can run them immediately the next time it happens.
4. Targeted additional recording such as blocked process reports is a configuration change. Set it up only with a clear reason, with limits and in coordination with operations; see the notes in the blocking diagnostic path.

Be honest about the gap: "The period was not recorded" is a valid finding and justifies better preparation.

Maintenance, jobs or write load are too slow

Is the job working, or is it waiting? And does the maintenance help the workload that follows? Four steps bring clarity:

1. Working or waiting? While the job is running, execute the blocking chain query and the query for waits in the time window. Many small commits, for example, show up as WRITELOG: each commit waits until the transaction log has been written.
2. Separate index maintenance from statistics maintenance. These are two different tasks with different goals.
3. Fragmentation alone is not a reason to act. The actual read and write load, page density and access path matter more.
4. Measure the effect on the workload that follows. A green maintenance job or a low fragmentation value does not yet tell you whether the next workload is faster. Compare cost and benefit.

Going deeper: our series Page Splits, Fill Factor, Fragmentation Explained, REORGANIZE or REBUILD? The Insert Test and SQL Server Indexes Without Maintenance: The Test. This guide does not yet cover backup, ETL and other Agent jobs.

Telling response time, CPU work and waiting apart

Almost every diagnosis eventually fails on the same mix-up: response time, CPU time and wait time are added together as if they were parts of the same stretch. The following diagram shows how they relate.

Four rules help when reading any measurement:

1. Response time depends on where you measure. A stopwatch in the application measures more than the duration of a statement in SQL Server.
2. CPU time can be larger than duration. In a parallel execution, several workers compute at the same time, and their CPU times are added up. Our CPU lab shows a query that finished in 89 instead of 289 milliseconds when run in parallel, and used more CPU in doing so.
3. The waits of many sessions overlap. Eight sessions that each wait three seconds at the same time add up to 24 seconds of wait time in three seconds of real time. Our waits lab shows this.
4. Nested calls count twice. The duration of a procedure includes the duration of the statements it calls. The article Query Store Duration for Triggers and Procedures demonstrates this with examples of our own.

So never add CPU time and wait times together into a supposed response time. Compare each quantity with itself: faster against slower period, before against after.

Did the change really help?

An action is a success only when the comparison holds up. After every change, check:

• The same business task with comparable data, parameters and result sets.
• Comparable load: same time of day, a similar workload mix, no additional jobs running.
• The same result: Does the query return the same data as before?
• Time and work: Look at duration, CPU time and pages read separately.
• Side effects: Do other operations get slower? Do memory requirements or write effort increase?
• Repetition: A single fast execution proves little for a sporadic problem. Observe several comparable time periods.

One example of this path from finding to re-measurement is our case study with statistics and a forced plan.

Examples from our investigations

These articles go deeper into individual questions from the diagnostic paths:

How to record your findings

A short, evidence-based finding helps you, your team and anyone you bring in later. Copy this template and fill it in:

Affected task:        Which process or job step is too slow?
Time frame:           From/to with time zone; current, past or reproduced?
Comparison:           Fast state or target; parameters and result set
Evidence:             Measurements with source (application, Query Store, DMV, plan) and limits
Working hypothesis:   Which explanation is supported by which evidence?
Next step:            Which observation or cross-check distinguishes the explanations?
Effect:               After a change: same result? Time, work, side effects?

Filled in for our blocking lab, it looks like this:

Affected task:        Updating row 2 (connection C) hangs
Time frame:           Reproduced in the lab, SQL Server 2022
Comparison:           Without the open transaction in A, the update completes immediately
Evidence:             Blocking chain query: path A <- B <- C; A sleeping with
                      1 open transaction; B and C waiting on LCK_M_X
Working hypothesis:   A keeps a transaction open after its UPDATE and locks row 1
Next step:            Work with the development team to find out why A does not finish
Effect:               After a rollback in A, B and C continued; watch for a recurrence

In the end, not every cause has to be proven. A clear difference between two states and a well-founded next test already move the investigation forward.