SQL Server: Why Is My Query Suddenly Slow?

Every DBA knows this situation: A SQL Server slow query was running just fine yesterday, and today the application suddenly seems to wait forever for the result.

At first glance, this looks like a classic performance problem. Very often, “the SQL Server” is blamed almost immediately. In practice, however, it is rarely that simple. A SQL Server slow query can have many different causes: blocking, parameter sniffing, outdated statistics, a changed execution plan, IO problems, memory pressure, or even an issue outside SQL Server.

This article is not meant to be a complete troubleshooting guide down to the last detail. Instead, I want to show the typical causes I would check first when a query suddenly becomes slow, and how to approach the analysis in a more structured way.

The list of possible causes is long and ranges from simple mistakes to difficult infrastructure problems:

• locks or blocking
• parameter sniffing
• outdated or misleading statistics
• changed execution plans
• memory or IO bottlenecks
• worker thread starvation
• missing, changed, or inefficient indexes
• network latency or issues outside SQL Server
• open transactions or a missing COMMIT
• parallelism-related problems
• configuration changes such as MAXDOP or Cost Threshold for Parallelism

1. Blocking, locks and open transactions

A very common scenario: Another session is holding an exclusive lock on a resource your query needs. This leads to waiting time that looks like “slowness” in the application, even though the query itself is not necessarily inefficient.

Locks are mechanisms SQL Server uses to coordinate concurrent access and maintain data consistency. There are different types of locks, such as shared locks for read operations and exclusive locks for write operations. If one session holds a lock and another session needs the same resource, blocking occurs. This can result in blocked processes, hanging queries and long wait times, even if CPU or IO usage does not look particularly high.

You can check this, for example, with:

SELECT *
FROM sys.dm_exec_requests
WHERE blocking_session_id <> 0;

The DMV sys.dm_exec_requests provides information about currently executing requests and is therefore a good first place to look when a query appears to be hanging or suddenly slow.

Another very useful option is sp_WhoIsActive — still one of my preferred tools for exactly this kind of analysis.

A case that is often overlooked: An application starts a transaction with BEGIN TRAN and forgets the COMMIT. The locks may then remain in place much longer than expected.

2. Parameter sniffing and changed execution plans

When a stored procedure is executed, SQL Server creates and caches an execution plan based on the parameter values used for that execution. If the same procedure is later executed with different parameter values, the cached plan may no longer be a good fit, especially when the data distribution is very uneven. This behavior is known as parameter sniffing.

For example, for a “small” parameter value, SQL Server creates an efficient plan using an Index Seek. Later, someone executes the same procedure with a “large” value that returns many more rows. As a result, the old plan is reused and suddenly becomes a poor choice. This can lead to Table Scans, longer runtime, higher memory usage, or significantly more IO.

Typical signs

• the same stored procedure has very different runtimes depending on the parameter value
• the execution plan shows unexpected Table Scans or Nested Loops for large result sets
• Estimated Rows and Actual Rows differ significantly
• a query is sometimes fast and sometimes slow, even though the code has not visibly changed

How to identify parameter sniffing in the execution plan

• check the “Parameter List” section in the execution plan
• compare Estimated Rows and Actual Rows
• use the Query Store to compare different plans for the same query

Possible actions

• use OPTION (RECOMPILE) carefully when a fresh plan per execution makes sense
• assign parameters to local variables inside the procedure if you deliberately want to reduce parameter sniffing effects
• use Query Store to monitor plans and, in selected cases, force a known good plan
• split stored procedures when there are known patterns, for example separate logic for “small” and “large” parameter ranges

It is important to keep in mind that parameter sniffing is not automatically bad. SQL Server uses parameter values on purpose to create good execution plans. It becomes a problem when a plan works well for one data distribution, but performs poorly for other parameter values.

Especially when a SQL Server slow query is not always slow, but only slow for certain parameter values, parameter sniffing should be relatively high on the checklist.

3. Outdated statistics or changed data distribution

With fast-growing tables, seasonal data, or highly skewed values, statistics may no longer represent the actual data distribution. In that case, row estimates and selectivity estimates can simply be wrong.

This matters because the optimizer bases its decisions on these estimates. Therefore, wrong estimates can quickly lead to the wrong access method, join strategy, or memory grant. If SQL Server expects only a few rows but actually has to process many rows, a plan that looked reasonable during optimization can become very expensive during execution.

You can check this, for example, with:

SELECT 
    OBJECT_NAME(object_id) AS table_name,
    name AS statistics_name,
    STATS_DATE(object_id, stats_id) AS last_updated
FROM sys.stats
ORDER BY last_updated ASC;

Possible actions:

• update statistics for the affected objects
• consider FULLSCAN where sampling is not good enough
• check whether maintenance jobs are actually running and effective
• pay attention to data distribution and modification patterns on large tables

With statistics, the date alone is not enough. A statistic can be old and still useful. It can also be relatively fresh and still cause problems if the data distribution is not helpful for the specific query.

4. Resource bottlenecks: IO, memory, and worker threads

Not every slow query is purely a query problem. In practice, SQL Server may simply be waiting for resources. This may involve storage, memory, CPU, network, or available worker threads.

• IO: Storage systems with high latency or contention on shared volumes can cause significant performance drops.
• Memory pressure: If the buffer cache is not sufficient, or if other processes take memory away from SQL Server, more data has to be read from disk.
• Worker thread starvation: If no worker threads are available, a request may wait even though CPU or IO does not look alarming at first glance.

A SQL Server slow query can therefore occur even when the SQL code itself has not changed, but the environment is currently waiting on IO, memory, or worker threads.

You can analyze wait times, for example, with:

SELECT *
FROM sys.dm_os_wait_stats
ORDER BY wait_time_ms DESC;

The DMV sys.dm_os_wait_stats is very useful, but it is not a finished diagnosis. Wait Stats show what SQL Server waited for. They do not automatically explain why those waits happened.

It can also be useful to look at currently waiting requests:

SELECT *
FROM sys.dm_exec_requests
WHERE status = 'suspended';

However, this is where interpretation matters. A high storage wait does not automatically mean that the storage is “the problem”. Instead, it may mean that an inefficient query reads far too much data and therefore makes the storage bottleneck visible.

5. Missing, removed, or inefficient indexes

If an index is missing, removed, or changed, the execution plan can change significantly. This often looks as if the query has suddenly become slow, even though the SQL statement itself has not changed.

A classic example is an index that was deleted because it looked “unused”, without checking whether it was important for rare but business-critical queries. Changed column order, a different fill factor, or highly skewed data distribution can also affect performance.

However, Index maintenance is only one part of the topic. Rebuild or Reorganize does not automatically fix every performance problem. Instead, when a query suddenly becomes slow, it is often more important to check whether the execution plan changed, whether statistics are accurate, and whether an index actually supports the access pattern of the query.

Useful questions:

• Was an index recently removed or changed?
• Did the execution plan change?
• Do Estimated Rows and Actual Rows match?
• Is an existing index no longer being used?
• Does Query Store show a plan change?

6. Parallelism: When MAXDOP and Cost Threshold do not fit the workload

SQL Server decides automatically whether a query should be executed in parallel. This decision is based on factors such as estimated resource usage, execution plan cost, and current system resources.

Problems can occur when the system configuration does not fit the workload, or when individual queries use more parallel workers than the overall system can reasonably handle. Settings like max degree of parallelism and cost threshold for parallelism are not values that should be copied blindly from a generic best-practice article.

As a result, the effect can go in both directions: Either too much CPU is used because too many parallel threads are started, or a query runs serially despite a large amount of data and becomes unnecessarily slow.

• too many parallel queries can put pressure on CPU and worker threads
• threshold values that are too low or not suitable for the workload may cause unnecessary parallelism
• settings that are too restrictive may prevent useful parallel execution
• the right values depend on workload, hardware, edition, and operating model

You can investigate this using sys.dm_exec_query_stats, sys.dm_exec_query_plan, Query Store, and of course the actual execution plan of the affected query.

Again, do not immediately change MAXDOP just because one query is slow. First check whether parallelism is really the root cause or only a visible symptom.

7. Network, tools, and external influences

Sometimes the root cause is not inside the query and not directly inside SQL Server. Especially with applications that return large result sets, remote connections, VPN links, or complex network paths, the actual delay may happen outside the database engine.

• DNS issues, VPN latency, firewalls, or network devices can cause unusual delays.
• SQL Server may be fast internally, but the frontend or a third-party tool still shows long wait times.
• Large result sets can make the query look finished on the SQL Server side, while the client is still receiving data.
• A classic SSMS mistake: Ctrl+E displays the estimated plan, while F5 actually executes the query.

The last point sounds trivial, but in practice it is worth not ignoring simple explanations completely. Not every “slow query” ends up being a deep engine problem.

Conclusion: Do not guess a SQL Server slow query — narrow it down

A query that suddenly becomes slow is rarely explained by a single assumption. Of course, a missing index can be the cause. But it can just as well be blocking, a changed execution plan, parameter sniffing, changed data distribution, or a bottleneck outside SQL Server.

For me, the important part is not to turn the wrong knob too early. Therefore, the sequence should be simple: first measure, then interpret, then change.

If you first look at active requests, blocking situations, waits, execution plans, statistics, and recent changes in the environment, you usually get a direction fairly quickly. That does not solve every problem immediately, but it helps avoid randomly changing indexes, MAXDOP, or server resources without understanding the actual cause.