Why does restarting SQL Server help, and why only briefly?

The restart really did help. But it does not prove why. A restart resets many things at once, and it does not tell you which of them made the difference. At the same time, it wipes out exactly the traces that could have led you to the cause.

This page is part of our SQL Server performance guide. It explains what a restart resets, which explanations for the improvement are possible, and which evidence you can save in five minutes before you restart.

What a restart resets

This page is about restarting the SQL Server service. Restarting the whole server also resets the operating system and every other program on the machine.

In our lab with SQL Server 2022 and 2025, this is exactly what we saw after an orderly restart: the execution statistics in the plan cache, the waits, and index usage all started again at zero. Of the test database's data pages, only 432 instead of 3,364 were still in memory, and the temporary objects were gone. Query Store, on the other hand, still held all 6,351 executions of the test procedure, and the error log and the system_health files were still there.

A restart changes neither the hardware nor the amount of data. If everything is fast afterward, both are sufficient, at least for a while. The cause then more likely lies in a state that the restart reset. More hardware can mask such a state, but it does not reliably remove it. And tests on a small test database that is “much faster” may mainly show that the optimizer chooses different plans there.

Five explanations for why it is faster afterward

Several of them can apply at the same time. The evidence from the next section helps you tell them apart.

1. An unsuitable plan was in the cache

SQL Server compiles each query into an execution plan and caches it so that it does not have to be computed again on the next call. The plan is created with the parameter values of the first call. If it suits a customer with three orders well, it can be very expensive for a customer with hundreds of thousands of orders, and vice versa. This behavior is called parameter sniffing.

The restart discards the plan. Which plan is created afterward is again decided by the first call. If a “suitable” value comes first, the problem seems solved, until the plan is recompiled for another reason, for example after a statistics update or because memory is tight, and an unfavorable value comes first. In this case, a restart is a gamble on which call comes first.

We reproduced this in the lab. An orders table with 1.25 million rows: one large customer has 800,000 orders, many small customers have one or five. A stored procedure summarizes a customer's orders (count, total, most recent order). After each restart, we called one customer first and then the other:

Reads here are logical reads: every access to an 8 KB page counts, even if the same page is read several times. That is why there can be more reads than the table has pages. What OPTION (RECOMPILE) costs in compile time is shown in Sometimes fast, sometimes slow; times below one millisecond have only coarse resolution.

After the first restart, the large customer suffered: almost six times as slow, with about 119 times as many reads as with a suitable plan. After the second, the small customer suffered: 12 reads became more than 20,000, and a fraction of a millisecond became more than 100 milliseconds. Because small customers usually account for far more calls, this variant in particular can slow down the entire application. Both times the restart was “successful,” just for different customers.

Incidentally, clearing the database's plan cache without a restart (ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE) was enough to produce the same effect. Some configuration changes also clear the plan cache, for example changes to max degree of parallelism, cost threshold for parallelism, or max server memory (Microsoft's list). If everything gets faster shortly after such a change, clearing the cache may have been the actual effect. And as long as a plan is in the cache, the order of calls no longer matters: the existing plan is reused.

How to recognize it: The same query has widely varying reads or durations (pages_min and pages_max in result 4 of the snapshot query), Query Store shows several plans for the same query, or the plan was created shortly before the slowdown (plan_created). Continue with: Sometimes fast, sometimes slow. There you will also find the countermeasures and what each of them costs.

2. A lock held up other sessions

A session began a transaction and did not finish it, for example because the application does not send a rollback after an error. Its locks hold up everyone who wants to change the same data, and those waiting hold up others in turn. The restart disconnects the session and rolls back the transaction. The chain is gone; the bug in the application is not.

How to recognize it: Wait types LCK_M_…, a chain in the blocked_by column, a sleeping session with a long-open transaction (result 3). Continue with: Blocking and timeouts.

3. A resource was used up

Large queries wait for memory for sorts and hash operations, a long-running transaction makes the version store in tempdb grow when row versioning is used (for example with Read Committed Snapshot), or tempdb runs full. After the restart, everything is free again, until the same process uses it up once more.

How to recognize it: Pending memory grants and large values for tempdb or the version store in result 1, wait types such as RESOURCE_SEMAPHORE. Continue with: Slow under load, Step 4.

4. The load was over anyway

The restart coincided with the end of the month-end close, or it canceled the large report that was slowing everything down. In that case, it was not the restart that helped but the end of the work. That work was canceled nonetheless: the report is missing, and changes that had begun were rolled back.

How to recognize it: Running requests in result 2, a comparison of the load before and after the restart, job lists, and application logs. Continue with: Slow under load, Step 1.

5. The cause was outside SQL Server

If the whole server was restarted, the operating system, other programs, drivers, and antivirus software were reset as well. With virtual machines, the situation on the host may also have changed.

How to recognize it: Does only a restart of the whole server help, but not a restart of the SQL Server service? Is another process using the CPU or memory? Slow under load, Step 2, has a pointer on this. Continue with: Server operations, virtualization, and monitoring.

Before you restart: five minutes for the evidence

Sometimes the restart is the right decision because the business has to keep running. Then it is worth investing five minutes first. Without this evidence, the search starts from zero again next time.

1. Note the time and the symptom. What is slow, since when, and who is affected? The findings sheet helps with this.
2. Save the snapshot. Run the following query and save the results before you restart.
3. If requests are hanging, save the chain. The blocking chain query shows which session is the head blocker.
4. Check Query Store. If it is turned on for the affected database, it keeps durations and plans across the restart. It first collects new values in memory and writes them to disk at intervals, every 15 minutes by default. Before an orderly shutdown, SQL Server writes the collected values to disk unless trace flag 7745 is set (Microsoft documentation). If the process ends abruptly instead, for example in a crash or when it is forcibly terminated, the values since the last save are missing, that is, up to 15 minutes. Run EXEC sys.sp_query_store_flush_db; in the affected database to write them to disk immediately. This requires the ALTER permission on the database (Microsoft documentation). In our lab, the last roughly 6,300 executions were missing after an abrupt termination; with a flush beforehand, all of them were preserved. While you are at it, check that Query Store is writing at all: actual_state_desc in sys.database_query_store_options should show READ_WRITE.
5. Look at open transactions. Result 3 shows how long transactions have been open and how much log they have written.

Query: Snapshot before a restart

/* PSG Stack · SQL Server Performance Guide · Query 4
   Snapshot before a restart: saves what a restart wipes out.

   - Read-only. Changes nothing, ends no session, resets no counters.
   - Every part runs with MAXDOP 1 so the diagnostic does not put
     additional parallel load on a server that is already busy.
   - Returns five result sets:
       1 Instance, memory and tempdb
       2 Running requests with wait type and plan
       3 Open transactions, including those of sleeping sessions
       4 Most expensive entries in the plan cache, with plan creation time
       5 Waits since the instance started (raw values)
   - Save the results before you restart: open each plan by clicking
     query_plan and save it as .sqlplan; save the other results with
     right-click "Save Results As".
   - Permissions: VIEW SERVER STATE
     (from SQL Server 2022, VIEW SERVER PERFORMANCE STATE is enough).
   - SQL Server 2016 and later. Tested in the PSG lab on 2017, 2019, 2022, and 2025.
   - The plan cache is shared by the whole instance; result 4 covers
     all databases. Change the sort order if needed, for example to
     duration instead of CPU. The values accumulate since the plan was
     created, so freshly compiled plans look small.
   - tempdb_free_mb covers only free space in the data files,
     not the tempdb transaction log.
*/

/* 1 · Instance, memory and tempdb */
SELECT
    @@SERVERNAME                                               AS server,
    i.sqlserver_start_time                                     AS started,
    SYSDATETIME()                                              AS captured_at,
    DATEDIFF(MINUTE, i.sqlserver_start_time, SYSDATETIME())    AS minutes_since_start,
    i.cpu_count                                                AS logical_cpus,
    i.committed_kb / 1024                                      AS memory_committed_mb,
    i.committed_target_kb / 1024                               AS memory_target_mb,
    pm.process_physical_memory_low                             AS memory_low_signaled,
    (SELECT COUNT(*) FROM sys.dm_exec_query_memory_grants
     WHERE grant_time IS NULL)                                 AS pending_memory_grants,
    tdb.tempdb_user_objects_mb,
    tdb.tempdb_internal_objects_mb,
    tdb.tempdb_version_store_mb,
    tdb.tempdb_free_mb,
    @@VERSION                                                  AS version
FROM sys.dm_os_sys_info AS i
CROSS JOIN sys.dm_os_process_memory AS pm
CROSS JOIN (
    SELECT
        SUM(user_object_reserved_page_count)     * 8 / 1024 AS tempdb_user_objects_mb,
        SUM(internal_object_reserved_page_count) * 8 / 1024 AS tempdb_internal_objects_mb,
        SUM(version_store_reserved_page_count)   * 8 / 1024 AS tempdb_version_store_mb,
        SUM(unallocated_extent_page_count)       * 8 / 1024 AS tempdb_free_mb
    FROM tempdb.sys.dm_db_file_space_usage
) AS tdb
OPTION (MAXDOP 1);

/* 2 · Running requests (excluding this session) */
SELECT
    r.session_id,
    r.status,
    r.blocking_session_id                                      AS blocked_by,
    r.wait_type,
    r.wait_time                                                AS waiting_ms,
    r.total_elapsed_time                                       AS running_ms,
    r.cpu_time                                                 AS cpu_ms,
    r.logical_reads                                            AS pages_read,
    r.granted_query_memory * 8 / 1024                          AS memory_grant_mb,
    DB_NAME(r.database_id)                                     AS database_name,
    s.host_name,
    s.program_name,
    s.login_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 statement,
    r.query_hash,
    r.plan_handle,
    r.query_plan_hash,
    p.query_plan                                               AS cached_plan
FROM sys.dm_exec_requests AS r
JOIN sys.dm_exec_sessions AS s ON s.session_id = r.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
OUTER APPLY sys.dm_exec_query_plan(r.plan_handle) AS p
WHERE s.is_user_process = 1
  AND r.session_id <> @@SPID
ORDER BY r.total_elapsed_time DESC
OPTION (MAXDOP 1);

/* 3 · Open transactions, including those of sleeping sessions */
SELECT
    s.session_id,
    s.status,
    st.is_user_transaction                                     AS user_transaction,
    at.transaction_begin_time                                  AS transaction_since,
    DATEDIFF(SECOND, at.transaction_begin_time, SYSDATETIME()) AS open_s,
    s.last_request_end_time                                    AS last_request_end,
    DB_NAME(dt.database_id)                                    AS database_name,
    CAST(dt.database_transaction_log_bytes_used / 1048576.0
         AS decimal(18,1))                                     AS log_mb,
    s.host_name,
    s.program_name,
    s.login_name
FROM sys.dm_tran_session_transactions AS st
JOIN sys.dm_tran_active_transactions AS at
     ON at.transaction_id = st.transaction_id
JOIN sys.dm_exec_sessions AS s
     ON s.session_id = st.session_id
LEFT JOIN sys.dm_tran_database_transactions AS dt
     ON dt.transaction_id = st.transaction_id
    AND dt.database_id <> 32767
WHERE st.session_id <> @@SPID
ORDER BY at.transaction_begin_time
OPTION (MAXDOP 1);

/* 4 · Most expensive entries in the plan cache */
SELECT TOP (20)
    qs.creation_time                                           AS plan_created,
    qs.last_execution_time                                     AS last_executed,
    qs.execution_count                                         AS executions,
    qs.total_worker_time / 1000                                AS cpu_ms_total,
    CAST(qs.total_worker_time / 1000.0 / qs.execution_count
         AS decimal(18,1))                                     AS cpu_ms_per_exec,
    CAST(qs.total_elapsed_time / 1000.0 / qs.execution_count
         AS decimal(18,1))                                     AS duration_ms_per_exec,
    CAST(qs.max_elapsed_time / 1000.0 AS decimal(18,1))        AS duration_ms_max,
    qs.total_logical_reads / qs.execution_count                AS pages_read_per_exec,
    qs.min_logical_reads                                       AS pages_min,
    qs.max_logical_reads                                       AS pages_max,
    DB_NAME(t.dbid)                                            AS database_name,
    OBJECT_NAME(t.objectid, t.dbid)                            AS object_name,
    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,
    qs.query_hash,
    qs.plan_handle,
    qs.query_plan_hash,
    p.query_plan
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS t
OUTER APPLY sys.dm_exec_query_plan(qs.plan_handle) AS p
ORDER BY qs.total_worker_time DESC
OPTION (MAXDOP 1);

/* 5 · Waits since the instance started (raw values to save) */
SELECT
    wait_type,
    waiting_tasks_count                                        AS wait_count,
    wait_time_ms                                               AS wait_ms,
    signal_wait_time_ms                                        AS signal_ms,
    max_wait_time_ms                                           AS max_wait_ms
FROM sys.dm_os_wait_stats
WHERE wait_time_ms > 0
ORDER BY wait_time_ms DESC
OPTION (MAXDOP 1);

Sample output from our lab (SQL Server 2022, shortened to the key columns and rows). One session held a transaction open and then did nothing, a second was waiting for its lock, and a third was computing:

-- 2 Running requests
session_id status    blocked_by wait_type   running_ms cpu_ms host_name statement
80         suspended 0          CXSYNC_PORT 26221      52430  hostC     SELECT COUNT_BIG(*) FROM dbo.t2 a CROSS JOIN …
86         suspended 56         LCK_M_X     25211      2      hostB     UPDATE [dbo].[t] set [v] = @1 WHERE [id]=@2

-- 3 Open transactions
session_id status   user_transaction open_s last_request_end        log_mb host_name
56         sleeping 1                29     2026-10-21 14:14:16.293 1.3    hostA
86         running  0                26     2026-10-21 14:14:19.400 0.0    hostB

-- 4 Most expensive entries in the plan cache (excerpt)
plan_created            executions cpu_ms_per_exec pages_min pages_max object_name statement
2026-10-21 14:14:07.137 3          3356.1          158911    158957    p_teuer     SELECT COUNT_BIG(*) FROM …
2026-10-21 14:14:06.760 10         29.7            2714      2714      p_mittel    SELECT v, COUNT_BIG(*) AS n …
2026-10-21 14:14:06.657 200        0.0             3         3         p_billig    SELECT v FROM dbo.t WHERE id = @id

How to read the output: session 86 is waiting with LCK_M_X for session 56. That session appears in result 3 as sleeping, with a transaction that has been open for 29 seconds: a sleeping head blocker as in Blocking and timeouts. Session 80 is running in parallel and has already used 52 seconds of CPU time in 26 seconds. In result 4, pages_min and pages_max show whether the same statement sometimes reads little and sometimes a lot; if they are far apart, look at parameters and the plan. For ad hoc statements, database_name and object_name stay empty; object_name also stays empty if your login has no access to the database in question. That session 86 is suspended in result 2 and running in result 3 is not a contradiction: result 2 shows the state of the request, result 3 that of the session. Result 5 usually starts with harmless background waits; you evaluate it later by comparing it with a second snapshot.

The query only reads and puts little load on the server. On very large instances, result 4 can take a few seconds because it scans the entire plan cache.

Often something smaller than a restart will do

If the evidence points to a single cause, there are more targeted interventions. Each of them is a change to a running system. Save the evidence first and coordinate the intervention with the operations team.

DBCC FREEPROCCACHE without an argument clears the plan cache of the entire instance. For the plans, that is almost as blunt as a restart, just without downtime, and it also destroys the evidence.

After the restart: keep tracking the cause

• Compare plans. Put the saved plan next to the new one. Does query_plan_hash differ? How many pages does the query read per execution now?
• Analyze Query Store. The query “Most expensive queries in a time window” shows the time before and after the restart. In SQL Server Management Studio, the Regressed Queries and Queries With High Variation reports also help.
• Be prepared for it to come back. Keep the snapshot query and the blocking chain query at hand. If the problem returns, you will have the comparison within minutes.

And if the restart helps regularly?

Some teams therefore restart SQL Server every week. As a deliberate stopgap with an end date, that can be justified. As a permanent solution, it has a price: downtime, empty memory and plans that must be recompiled after every start, lost diagnostic traces, and a cause that keeps growing. A maintenance window for the restart is also a good time to save the evidence.

Record the finding

For our lab test, the findings sheet would look like this:

Affected task:        A customer's order summary is slow, fast again after a restart
Time frame:           Reproduced in the lab, SQL Server 2022
Comparison:           Large customer 1,777 ms after "small first", 302 ms with its own plan
Evidence:             Procedure statistics and plan: the same plan (seek + key lookup)
                      for both customers; 12 reads for the small one, 2.4 million for
                      the large one; after a restart with "large first", the reverse
Working hypothesis:   Parameter sniffing: the plan of the first call does not suit
                      the large customer
Next step:            Test a countermeasure for both customer classes on the same data
                      (see "Sometimes fast, sometimes slow", Step 5)
Effect:               After the change, measure both customers in any order,
                      including after another restart

More diagnostic paths