SQL Server Is Sometimes Fast and Sometimes Slow

Fluctuating SQL Server performance can have several causes: a different parameter requires more work. SQL Server uses a different execution plan. Or the same plan runs while other activity occupies resources or holds locks.

The first comparison is therefore: Is SQL Server doing different work, or is comparable work taking longer under different conditions?

Why the same query can have different execution plans

An execution plan describes how SQL Server processes a query: which access methods it uses, how it joins tables and which steps follow.

When compiling, the optimizer estimates, among other things, how many rows it has to process. For many parameterized queries, it can take the current parameter values into account. This is called parameter sniffing. A plan that has already been compiled can then be reused for later calls.

This saves compilation work. It can become a problem when data volumes differ widely: an access method that is cheap for a customer with ten matching rows can become expensive for another customer with 900,000 matching rows. The parameter value changes; the reused plan does not necessarily change with it. Microsoft explains parameter values at compile time and plan reuse.

Parameter sniffing is therefore one possible explanation. To check it for your case, start by comparing the actual calls.

Step 1: Compare a fast call with a slow call

Choose the same business task and write down the following for both states:

• time and measured duration;
• filters and parameters used, such as customer number or date range;
• volume of data requested and returned;
• affected application and database;
• known changes and larger tasks running at the same time.

Check: Are you really comparing the same work? A search over one day and a search over five years can use the same SQL text and still process very different amounts of data.

Fast in SSMS, slow in the application

You hear this often. Two causes are especially common:

1. Different connection settings. SQL Server Management Studio sets ARITHABORT ON, while many application drivers work with ARITHABORT OFF. Because this setting is part of the plan cache key, the same query from SSMS gets its own cache entry and is compiled again with your test value. You then see a different plan than the application does.
2. Local variables instead of parameters. If you rebuild the query for testing with DECLARE @Customer int = 42;, you measure something different: the optimizer does not know the value of a local variable at compile time. It works with an average estimate, similar to OPTIMIZE FOR UNKNOWN.

Test with the same settings and real parameters, for example by calling the procedure or using sp_executesql. Also check that both runs use the same database and retrieve the complete result.

Your first finding: You can describe which conditions were the same and which still differ. Missing parameter values remain an open question.

Step 2: Check the existing history in Query Store

Query Store can keep earlier plans and runtime statistics for a database. This lets you examine whether a query has behaved differently since a certain point in time.

First check whether it has any data at all. Starting with SQL Server 2022, Query Store is enabled by default for newly created databases. Older versions and databases migrated from them often do not have it active.

In SQL Server Management Studio, open the properties of the affected database and go to Query Store. Check the actual operation mode. If it is unexpectedly set to Read Only, the maximum size has often been reached. Existing data remains readable, but new data is no longer collected. The readonly_reason column shows the cause. Microsoft describes the Query Store states.

If history is available:

1. Under the database, open the Query Store folder.
2. If you have observed a slowdown, start with Regressed Queries and narrow down the comparison periods.
3. Identify the statement that belongs to the affected task. Check the SQL text and, if applicable, the calling procedure.
4. Look at which plans were used in the fast and in the slow period. If you select two plans, the report can show them side by side graphically. Microsoft explains the Regressed Queries report.

An empty report does not prove that there were no slow executions. The time range, filters and capture scope can hide the case you are looking for. In the usual capture mode, AUTO, Query Store skips rare and very inexpensive queries, for example.

Query: Most expensive queries in a time window

Instead of using the reports, you can also query Query Store directly. The following query lists the queries with the highest CPU consumption for a time window. Run it once for the fast period and once for the slow period.

/* PSG Stack · SQL Server Performance Guide · Query 1
   Query Store: Which queries used the most CPU in the time window?

   - Read-only. Changes no settings and no data.
   - Runs with MAXDOP 1 so the diagnostic does not put additional
     parallel load on the server.
   - Run it in the affected database (USE ...). Query Store must have
     collected data there for the period in question.
   - Permissions: VIEW DATABASE STATE
     (from SQL Server 2022, VIEW DATABASE PERFORMANCE STATE is enough).
     Without permissions on the object (for example VIEW DEFINITION),
     the object_name column stays empty. Without the state permission,
     the query fails with error 262.
   - SQL Server 2016 and later. Tested in the PSG lab on 2017, 2019, 2022, and 2025.
   - Enter the times with a time zone offset; Query Store stores UTC.
   - Values are aggregated over Query Store intervals, not single executions.
*/
DECLARE @from datetimeoffset = '2026-10-21 08:00 +02:00',   -- start of the time window
        @to   datetimeoffset = '2026-10-21 09:00 +02:00';   -- end of the time window

SELECT TOP (20)
    q.query_id,
    OBJECT_NAME(q.object_id)                                       AS object_name,
    COUNT(DISTINCT rs.plan_id)                                     AS plans,
    SUM(rs.count_executions)                                       AS executions,
    SUM(CASE WHEN rs.execution_type <> 0
             THEN rs.count_executions ELSE 0 END)                  AS aborted_or_failed,
    CAST(SUM(rs.avg_cpu_time * rs.count_executions) / 1000.0
         AS decimal(18,1))                                         AS cpu_ms_total,
    CAST(SUM(rs.avg_cpu_time * rs.count_executions)
         / NULLIF(SUM(rs.count_executions), 0) / 1000.0
         AS decimal(18,2))                                         AS cpu_ms_per_exec,
    CAST(SUM(rs.avg_duration * rs.count_executions)
         / NULLIF(SUM(rs.count_executions), 0) / 1000.0
         AS decimal(18,2))                                         AS duration_ms_per_exec,
    CAST(SUM(rs.avg_logical_io_reads * rs.count_executions)
         / NULLIF(SUM(rs.count_executions), 0)
         AS decimal(18,0))                                         AS pages_read_per_exec, -- logical reads, 8-KB pages
    LEFT(qt.query_sql_text, 200)                                   AS sql_text_start
FROM sys.query_store_runtime_stats AS rs
JOIN sys.query_store_runtime_stats_interval AS i
     ON i.runtime_stats_interval_id = rs.runtime_stats_interval_id
JOIN sys.query_store_plan AS p        ON p.plan_id = rs.plan_id
JOIN sys.query_store_query AS q       ON q.query_id = p.query_id
JOIN sys.query_store_query_text AS qt ON qt.query_text_id = q.query_text_id
WHERE i.start_time < @to
  AND i.end_time   > @from
GROUP BY q.query_id, q.object_id, qt.query_sql_text
ORDER BY cpu_ms_total DESC
OPTION (MAXDOP 1);

Example output from our lab (SQL Server 2022, trimmed to the most important columns and rows):

query_id object_name plans executions aborted_or_failed cpu_ms_total cpu_ms_per_exec pages_read_per_exec
5        p_teuer     1     5          0                 5618.2       1123.63         415586
6        p_lang      1     2          2                 3998.5       1999.23         425956
3        p_billig    1     50         0                 .8           .02             3
7        p_fehler    1     3          3                 .7           .23             3

p_teuer leads with about 5.6 seconds of CPU. p_billig ran ten times as often but is unremarkable. p_lang was aborted both times by an application timeout, and p_fehler ended with an error three times.

Here is how to read the result:

• plans greater than 1: This query had several plans in the window. A candidate for Step 3.
• cpu_ms_per_exec and pages_read_per_exec show the effort per call; executions shows how often it ran. If mainly the number of calls rises in the slow period, the Slow under load path can help you further.
• aborted_or_failed counts executions that were aborted, for example by an application timeout, or that ended with an error. If they accumulate, the Blocking and timeouts path is worth following.

For each plan and period, compare at least duration, CPU time, logical reads and the number of calls. Logical reads count 8-KB pages read from memory; they are not a count of physical disk accesses. Pay attention to the units: the catalog views store duration and CPU in microseconds and reads as a page count, while the SSMS reports show milliseconds and kilobytes. The number of calls helps you interpret a change in total consumption. Microsoft documents the runtime metrics.

Query Store aggregates measurements in time intervals. An average of 100 milliseconds therefore does not mean that every individual call took 100 milliseconds. The parameter values of each individual call cannot be reconstructed from it. How Query Store collects its data.

If the period you are looking for was not captured, document that gap. Enabling capture later cannot record the past incident retroactively.

Step 3: Look at execution plans and parameters together

If the plans differ, check whether the differences match the effort you observed.

A common case is an Index Seek with Key Lookups. SQL Server finds matching entries directly in an index and fetches additional required columns through further individual lookups. For a few matches, this is cheap. For a very large number of matches, these individual lookups can become more expensive than a scan, which reads the entire table or index.

Check the following:

1. How many matching rows were estimated for the relevant access?
2. How many rows were actually processed? Only an Actual Plan shows this, that is, an execution plan with measured row counts.
3. For which parameter value was the plan compiled, and which value did the slow call run with?
4. Does the access method explain the additional reads or the higher CPU work?

The plan itself answers question 3. In the graphical plan, click the top operator (for example SELECT) and open its properties. Under Parameter List, each parameter shows its Parameter Compiled Value, which is the value the optimizer used for its estimates. In an Actual Plan, the Parameter Runtime Value of the measured call appears next to it. If the two are far apart and the estimate fits only the first value, you have a clear indication of parameter sniffing.

The plan stored in Query Store also contains the compiled parameter value and the estimates. However, it is not an Actual Plan with the actual row counts of the earlier executions. A newly captured Actual Plan belongs to its own execution. Microsoft describes the plans stored in Query Store.

Example from our test with different parameters

In a synthetic test, the same table contained ten matching rows for one customer and 900,000 for another. The query calculated a sum in each case. Many processed table rows therefore did not mean many returned result rows here.

For the customer with 900,000 matching rows, we compared two plan origins. Switch between the two actual plans:

The first plan estimated ten matches and fetched each of the 900,000 rows individually. The second was compiled for the large number of matches and read the table once. The decisive measure is the amount of work: almost a hundred times as many reads. For the small case, on the other hand, the scan was unnecessarily expensive: 89 instead of less than 1 millisecond.

What you can take from this for your diagnosis: A seek is not an automatic seal of approval. What matters is how much work the specific plan causes with the current data and parameters.

On SQL Server 2022 and later with compatibility level 160, SQL Server can create several plan variants itself for cases like these (Parameter Sensitive Plan Optimization, or PSP for short). In Query Store, they then appear as variants of the same query. If you reproduce our example on a current system, you may therefore see a different picture.

For a more detailed explanation with examples, see Parameter Sniffing in SQL Server Explained.

If the same query is fast on another server, for example in test, our article sp_BlitzPlanCompare: Fast in Test, Slow in Production shows how to compare plans, parameters and statistics between the two servers and use a cross-check to confirm whether a difference is the cause.

Step 4: If the plan is the same, look for changed conditions

An unchanged plan can still have different runtimes. Classify further observations:

A long duration with little CPU fits blocking, for example, but does not yet prove it. For that, you need time-matched evidence of who waited on which resource.

Also watch for nested calls: an outer statement can include the time of a trigger or a function. The times of the inner and outer statements then cannot simply be added up to a total duration. Our article Query Store Duration for Triggers and Procedures shows such measurement limits using our own examples.

Step 5: Derive the next action from your finding

Your explanation should now be more precise than "SQL Server is slow sometimes."

If plan and parameters together explain the additional effort, several countermeasures are possible. Each has its price:

In our parameter lab, OPTION (RECOMPILE) worked in both directions: about 212 milliseconds in the large case and 2.4 milliseconds in the small case, compared with less than 1 millisecond without recompilation. OPTIMIZE FOR UNKNOWN, on the other hand, is not a general switch against fluctuating performance: there, the average estimate still led to many lookups in the large case. Your tests must therefore always include both small and large parameter cases.

Also check whether a plan is already being forced. Plan forcing means that SQL Server is instructed to use a specified plan. Document why and since when this setting has existed, and whether it still fits the current situation.

In an anonymized real-world case, a new statistics object existed, but the expected improvement did not appear at first. A previously forced plan kept the old access path in place. After the forcing was removed and the behavior observed, a different plan could be used. The average runtime of the examined function dropped from about 15 to 0.2 milliseconds. That was a measured function runtime, not a measurement of the full application response. The case study with statistics and a forced plan describes the sequence.

For your own case, the rule is: first secure the current state and the comparison data, then plan a limited test. Two shortcuts often lead you astray here:

• Clearing the plan cache. DBCC FREEPROCCACHE without an argument removes the plans of the entire instance; all queries then have to be compiled again, and your comparison baseline is gone. You can target a single plan with its plan_handle, or use sp_recompile for a procedure.
• "The statistics update helped." A statistics update invalidates dependent plans, and the query is compiled again. If a favorable parameter value happens to come in at that moment, it looks like a fix. The next unfavorable value brings the problem back. After every action, therefore, check which value the new plan was compiled with.

Restarting SQL Server is the same shortcut on a larger scale: it discards all plans, and the first call afterward decides which plan stays. Why that helps only briefly and what you should save beforehand is shown with a lab test in Why does restarting SQL Server help, and why only briefly?.

Record your finding and check the effect

A usable investigation status can look like this:

The slow call processes a significantly larger number of matches. It uses a plan compiled for few matches, with many additional lookups. In the comparison, there are substantially more logical reads. Next, we will test both parameter classes on the same data state with a deliberately modified variant.

After a change, compare the same business task again. Check duration, CPU work, reads and result, as well as possible effects on other activity. Repeat the observation under comparable conditions. A single fast call after the change is not enough to consider a sporadic problem solved.

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

More diagnostic paths