Troubleshoot a Slow SQL Procedure Explained with Examples

Scenario

A stored procedure previously completed in 2 seconds, but now takes 2 minutes.

This usually means something changed in the execution environment, data distribution, execution plan, blocking, statistics, indexes, or parameters. Troubleshoot systematically instead of immediately rebuilding indexes or modifying the procedure.

1. Confirm and measure the slowdown

Execute the procedure using the same parameters that caused the issue.

SET STATISTICS IO ON;
SET STATISTICS TIME ON;

EXEC dbo.GetCustomerOrders
    @CustomerId = 1001,
    @FromDate = '2026-01-01';

SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;

Check:

CPU time
Elapsed time
Logical reads
Physical reads
Rows returned
Messages or warnings

How to interpret the result

Observation	Likely issue
High CPU time	Expensive joins, sorting, aggregation, scans or parallelism
High elapsed time but low CPU	Blocking, waits, network or I/O delay
High logical reads	Inefficient execution plan or missing index
High physical reads	Data pages not cached or storage pressure
Runtime differs by parameter	Parameter-sensitive plan or parameter sniffing
Excessive rows returned	Changed data volume or insufficient filtering

2. Check whether the procedure is blocked

Before changing the query, confirm that it is actually running slowly and not waiting for another transaction.

SELECT
    r.session_id,
    r.status,
    r.command,
    r.blocking_session_id,
    r.wait_type,
    r.wait_time,
    r.wait_resource,
    r.cpu_time,
    r.logical_reads,
    r.reads,
    r.writes,
    r.total_elapsed_time,
    DB_NAME(r.database_id) AS DatabaseName,
    t.text AS RunningSql
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE r.session_id <> @@SPID;

Important columns:

blocking_session_id: identifies the blocking session.
wait_type: explains what SQL Server is waiting for.
wait_resource: identifies the locked or unavailable resource.
cpu_time: indicates CPU consumption.
total_elapsed_time: total time since execution started.

If blocking_session_id is not zero, investigate the blocker:

SELECT
    session_id,
    login_name,
    host_name,
    program_name,
    status,
    open_transaction_count
FROM sys.dm_exec_sessions
WHERE session_id = 75; -- blocking session

Common causes include:

Long-running transactions
Uncommitted transactions
Large UPDATE or DELETE operations
Inappropriate transaction scope
Application connections left open
Schema modification locks

Do not immediately kill a blocking session in production. First identify its purpose and estimate the rollback impact.

3. Capture the actual execution plan

The actual execution plan shows how SQL Server executed the procedure and includes runtime row counts.

In SQL Server Management Studio:

Query → Include Actual Execution Plan

Then run:

EXEC dbo.GetCustomerOrders
    @CustomerId = 1001,
    @FromDate = '2026-01-01';

Inspect the plan for:

Table scans and clustered-index scans
Expensive key lookups
Incorrect join types
Large sorts
Hash operations
Estimated-versus-actual row differences
Missing-index suggestions
Implicit conversions
Sort or hash spills
Excessive memory grants
Serial versus parallel execution
Warnings on operators

Estimated versus actual rows

Suppose an operator shows:

Estimated rows: 10
Actual rows:    1,000,000

SQL Server expected only 10 rows but processed one million. This large estimation error can cause SQL Server to choose:

Nested-loop joins instead of hash joins
Insufficient memory grants
Repeated key lookups
Incorrect join order
Serial processing
TempDB spills

Likely causes include outdated statistics, skewed data, parameter sniffing, or non-SARGable conditions.

4. Compare the current plan with the previous good plan

If Query Store is enabled, compare the historical fast plan with the current slow plan.

SELECT actual_state_desc
FROM sys.database_query_store_options;

If Query Store contains the procedure’s history, check:

Whether the execution plan changed
Average duration before and after the change
CPU time
Logical reads
Number of executions
Wait statistics
Plan IDs
When the regression started

A typical pattern might be:

Plan	Average duration	Logical reads	Result
Old plan	2 seconds	5,000	Good
New plan	120 seconds	2,000,000	Regressed

This confirms a plan regression.

As a short-term production mitigation, a previously known good plan can be forced through Query Store. However, forcing a plan should be treated as stabilization, not necessarily the permanent fix.

5. Investigate parameter sniffing

Parameter sniffing is one of the most common reasons a procedure is fast for some parameter values and slow for others.

SQL Server normally compiles a stored procedure using the parameter values supplied during compilation. The generated plan is then cached and reused.

Consider:

CREATE PROCEDURE dbo.GetOrders
    @CustomerId INT
AS
BEGIN
    SELECT OrderId, CustomerId, OrderDate, TotalAmount
    FROM dbo.Orders
    WHERE CustomerId = @CustomerId;
END;

Suppose:

Customer 10 has 5 orders.
Customer 500 has 2 million orders.

A plan compiled for customer 10 may use an index seek and nested-loop join. That plan may perform poorly when reused for customer 500.

Test for parameter sniffing

Run the procedure with different parameters:

EXEC dbo.GetOrders @CustomerId = 10;
EXEC dbo.GetOrders @CustomerId = 500;

Test recompilation:

EXEC dbo.GetOrders
    @CustomerId = 500
WITH RECOMPILE;

If the procedure is fast with WITH RECOMPILE but slow without it, plan reuse or parameter sensitivity is likely involved.

You can also inspect the parameter values stored in the execution plan:

Parameter compiled value
Parameter runtime value

A large difference between them is an important clue.

Possible solutions

Statement-level recompilation

SELECT OrderId, CustomerId, OrderDate, TotalAmount
FROM dbo.Orders
WHERE CustomerId = @CustomerId
OPTION (RECOMPILE);

This produces a plan for each execution. It is useful when execution frequency is moderate and parameter distributions vary significantly.

Optimize for a typical value

SELECT OrderId, CustomerId, OrderDate, TotalAmount
FROM dbo.Orders
WHERE CustomerId = @CustomerId
OPTION (OPTIMIZE FOR (@CustomerId = 1001));

Use this only when the specified value accurately represents common usage.

Optimize for unknown

SELECT OrderId, CustomerId, OrderDate, TotalAmount
FROM dbo.Orders
WHERE CustomerId = @CustomerId
OPTION (OPTIMIZE FOR UNKNOWN);

This uses general statistical estimates rather than the current parameter value.

Separate query paths

IF @CustomerId = 500
BEGIN
    -- Query designed for high-volume customers
END
ELSE
BEGIN
    -- Query designed for normal customers
END;

This is useful when the data has clearly different distributions.

Avoid blindly copying parameters into local variables. It may hide parameter values during compilation but often produces a generic and potentially inefficient plan.

6. Check statistics

Statistics help SQL Server estimate how many rows a query will return. When statistics are outdated, SQL Server may choose a poor execution plan.

Check statistics information:

SELECT
    OBJECT_NAME(s.object_id) AS TableName,
    s.name AS StatisticsName,
    sp.last_updated,
    sp.rows,
    sp.rows_sampled,
    sp.modification_counter
FROM sys.stats AS s
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) AS sp
WHERE s.object_id = OBJECT_ID('dbo.Orders');

Pay attention to:

last_updated
rows
rows_sampled
modification_counter

Update specific statistics:

UPDATE STATISTICS dbo.Orders IX_Orders_CustomerId;

Or update all statistics for the table:

UPDATE STATISTICS dbo.Orders;

For an important skewed table, a full scan may produce better statistics:

UPDATE STATISTICS dbo.Orders
WITH FULLSCAN;

However, FULLSCAN can consume significant I/O and CPU on a large table. Test and schedule it appropriately.

Updating statistics can invalidate the old plan, allowing SQL Server to compile a better one.

7. Check for missing, changed or unusable indexes

Verify whether an index used by the old plan was:

Dropped
Disabled
Changed
Rebuilt with different columns
Replaced by another index
Made less selective by increased data volume

Check existing indexes:

SELECT
    i.name AS IndexName,
    i.type_desc,
    i.is_unique,
    i.is_disabled,
    COL_NAME(ic.object_id, ic.column_id) AS ColumnName,
    ic.key_ordinal,
    ic.is_included_column
FROM sys.indexes AS i
INNER JOIN sys.index_columns AS ic
    ON i.object_id = ic.object_id
   AND i.index_id = ic.index_id
WHERE i.object_id = OBJECT_ID('dbo.Orders')
ORDER BY i.name, ic.key_ordinal, ic.index_column_id;

Example

Query:

SELECT OrderId, OrderDate, TotalAmount
FROM dbo.Orders
WHERE CustomerId = @CustomerId
  AND OrderDate >= @FromDate;

A useful covering index might be:

CREATE INDEX IX_Orders_CustomerId_OrderDate
ON dbo.Orders(CustomerId, OrderDate)
INCLUDE (TotalAmount);

This may allow SQL Server to use an index seek and return the required columns without additional key lookups.

Do not create every index suggested by the execution plan. First check for overlapping or duplicate indexes and consider the extra cost to INSERT, UPDATE and DELETE.

8. Check index fragmentation carefully

Fragmentation may affect large range scans, especially when storage reads are involved. However, fragmentation is not the first explanation for every slow query.

SELECT
    OBJECT_NAME(ips.object_id) AS TableName,
    i.name AS IndexName,
    ips.index_type_desc,
    ips.avg_fragmentation_in_percent,
    ips.page_count
FROM sys.dm_db_index_physical_stats
(
    DB_ID(),
    OBJECT_ID('dbo.Orders'),
    NULL,
    NULL,
    'LIMITED'
) AS ips
INNER JOIN sys.indexes AS i
    ON ips.object_id = i.object_id
   AND ips.index_id = i.index_id;

Consider both:

Fragmentation percentage
Page count

A high fragmentation percentage on a very small index may not matter.

Typical maintenance options are:

ALTER INDEX IX_Orders_CustomerId
ON dbo.Orders
REORGANIZE;

or:

ALTER INDEX IX_Orders_CustomerId
ON dbo.Orders
REBUILD;

A rebuild also refreshes index statistics, which is sometimes the real reason performance improves afterward.

9. Look for non-SARGable conditions

A condition is SARGable when SQL Server can efficiently use an index to locate rows.

Non-SARGable

WHERE YEAR(OrderDate) = 2026;

The function is applied to the indexed column, so SQL Server may scan many rows.

SARGable

WHERE OrderDate >= '20260101'
  AND OrderDate <  '20270101';

10. Check for implicit conversions

An implicit conversion can prevent an index seek.

Suppose the database column is VARCHAR, but the procedure parameter is NVARCHAR:

CREATE PROCEDURE dbo.FindCustomer
    @CustomerCode NVARCHAR(20)
AS
BEGIN
    SELECT *
    FROM dbo.Customers
    WHERE CustomerCode = @CustomerCode;
END;

If CustomerCode is VARCHAR(20), SQL Server may convert the column during comparison.

Define matching data types:

CREATE PROCEDURE dbo.FindCustomer
    @CustomerCode VARCHAR(20)
AS
BEGIN
    SELECT *
    FROM dbo.Customers
    WHERE CustomerCode = @CustomerCode;
END;

Check that parameters and columns match in:

Data type
Length
Unicode versus non-Unicode
Precision and scale
Collation, where relevant

The execution plan may show a CONVERT_IMPLICIT warning.

11. Check whether data volume or distribution changed

A query that was efficient for 10,000 rows may not remain efficient for 100 million rows.

Check table size:

SELECT
    SUM(p.rows) AS ApproximateRows
FROM sys.partitions AS p
WHERE p.object_id = OBJECT_ID('dbo.Orders')
  AND p.index_id IN (0, 1);

Also investigate:

Whether one customer now owns a large percentage of rows
Whether the date range has increased
Whether archived records were imported
Whether the procedure now returns more columns or rows
Whether join relationships have become one-to-many
Whether recent data differs significantly from historical data

Skewed data can make one cached plan unsuitable for every parameter.

12. Examine waits and resource pressure

A good execution plan can still run slowly if the server is under pressure.

Common wait types include:

Wait category	Possible meaning
`LCK_M_*`	Locking or blocking
`PAGEIOLATCH_*`	Waiting for database pages from storage
`WRITELOG`	Transaction-log write delays
`RESOURCE_SEMAPHORE`	Waiting for a query memory grant
`CXPACKET` / `CXCONSUMER`	Parallel execution activity or imbalance
`SOS_SCHEDULER_YIELD`	CPU pressure
`ASYNC_NETWORK_IO`	Client is consuming results slowly
`PAGELATCH_*`	In-memory page contention, possibly TempDB
`THREADPOOL`	Worker-thread pressure

Waits must be interpreted in context. For example, CXPACKET does not automatically mean parallelism should be disabled.

Also check:

CPU utilization
Available memory
Disk latency
TempDB activity
Transaction-log performance
Other heavy queries
Scheduled jobs
Backups or maintenance operations
Number of concurrent executions

If it is fast during quiet hours but slow during peak hours, the stored procedure might not be the only cause.

13. Check sort and hash spills

Sort and hash operations require memory. If SQL Server grants insufficient memory, data may spill to TempDB.

The actual execution plan may show warnings such as:

Operator used TempDB to spill data during execution

Spills can be caused by:

Incorrect cardinality estimates
Outdated statistics
Large sorts
Large hash joins
Wide rows
Insufficient query memory
Memory pressure caused by concurrent queries

Possible fixes include:

Updating statistics
Reducing rows earlier in the query
Selecting only required columns
Adding an appropriate index
Improving join predicates
Correcting estimation problems
Reducing unnecessary sorting

14. Inspect recent changes

Determine when the slowdown started and compare it with recent changes.

Investigate:

Stored-procedure deployment
Table or index changes
Statistics updates
Schema changes
Compatibility-level changes
SQL Server patch or upgrade
Database restore or migration
Data import
Application parameter changes
Configuration changes
Increased concurrency
New scheduled jobs
Query Store plan changes

Retrieve the procedure definition:

EXEC sys.sp_helptext 'dbo.GetCustomerOrders';

Check its modification date:

SELECT
    name,
    create_date,
    modify_date
FROM sys.procedures
WHERE object_id = OBJECT_ID('dbo.GetCustomerOrders');

A database compatibility-level change can affect cardinality estimation and plan selection:

SELECT
    name,
    compatibility_level
FROM sys.databases
WHERE name = DB_NAME();

15. Test whether recompilation fixes the problem

For diagnosis:

EXEC sys.sp_recompile 'dbo.GetCustomerOrders';

Or:

EXEC dbo.GetCustomerOrders
    @CustomerId = 1001
WITH RECOMPILE;

If performance returns to two seconds, it suggests the cached plan was unsuitable.

However, recompiling is not always a permanent solution. The next execution could cache another poor plan. Find the underlying cause—such as stale statistics or parameter-sensitive behavior.

Avoid clearing the entire plan cache in production:

-- Avoid as a routine fix:
DBCC FREEPROCCACHE;

Clearing the complete cache forces many unrelated queries to recompile, potentially causing a CPU spike and hiding the real problem.

16. Review the stored procedure’s query design

Look for:

SELECT *
Unnecessary joins
Cursor or row-by-row processing
Scalar functions called for every row
Multi-statement table-valued functions
Large table variables
Temporary tables without useful indexes
Repeated access to the same tables
Unnecessary DISTINCT
Unnecessary sorting
Leading-wildcard searches
Optional-parameter patterns
Excessive dynamic SQL
Incorrect join predicates
Missing predicates

Optional-parameter problem

WHERE (@CustomerId IS NULL OR CustomerId = @CustomerId)
  AND (@Status IS NULL OR Status = @Status);

This pattern supports flexible searches but may produce poor estimates and inefficient reusable plans.

Depending on the situation, alternatives include:

Parameterized dynamic SQL
Separate query branches
OPTION (RECOMPILE)
Separate procedures for significantly different search patterns

17. Check temporary objects and intermediate row counts

Stored procedures often use temporary tables or table variables.

Temporary table

CREATE TABLE #Orders
(
    OrderId INT,
    CustomerId INT,
    OrderDate DATE
);

If it contains many rows and is later joined by CustomerId, it may require an index:

CREATE INDEX IX_TempOrders_CustomerId
ON #Orders(CustomerId);

Also inspect whether an intermediate query unexpectedly inserts millions of rows.

Check row counts during controlled testing:

SELECT COUNT(*) AS TempRowCount
FROM #Orders;

A procedure may become slow because an earlier filtering condition changed or no longer eliminates enough rows.

18. Validate the fix

After identifying the cause, test before and after using the same representative parameters.

Record:

Metric	Before	After
Duration	120 sec	2 sec
CPU time	80 sec	1 sec
Logical reads	2,000,000	8,000
Returned rows	1,000	1,000
Plan	Scan + lookup	Seek
TempDB spills	Yes	No

Test several parameter types:

Common value
Rare value
High-volume value
Small date range
Large date range
Empty-result value
NULL, when supported

A fix that improves one parameter but damages all other executions is not a successful fix.

Recommended troubleshooting sequence

Reproduce the issue with the same parameters.
Capture duration, CPU and logical reads.
Check blocking and current wait type.
Capture the actual execution plan.
Compare estimated and actual row counts.
Compare the current plan with the historical good plan in Query Store.
Test for parameter sniffing using different parameters and WITH RECOMPILE.
Check statistics and data distribution.
Verify indexes and recent schema changes.
Check implicit conversions and non-SARGable predicates.
Inspect memory grants, spills and server resource pressure.
Apply the smallest safe fix.
Validate it with representative parameters and production-like data.
Monitor performance after deployment.

Interview-ready answer

I would first reproduce the issue using the same parameters and capture STATISTICS IO, STATISTICS TIME, and the actual execution plan. I would check whether the procedure is genuinely consuming resources or waiting because of blocking. I would then compare the current execution plan with the previous good plan through Query Store, focusing on scans, key lookups, spills, implicit conversions, and estimated-versus-actual row differences.

Next, I would test for parameter sniffing by running representative parameters and using WITH RECOMPILE for diagnosis. I would verify statistics, indexes, data growth, data skew and recent deployments. I would also inspect server waits and resource pressure. After identifying the root cause, I would apply the smallest appropriate fix, test it against multiple parameter values, and monitor it through Query Store.

Key points

Begin with evidence, not assumptions.
Always use the same problematic parameters when reproducing the issue.
High elapsed time does not always mean the query is inefficient; it may be blocked.
Compare the current plan with the historical good plan.
Large estimated-versus-actual row differences are important clues.
Parameter sniffing is common when data distribution is uneven.
Update statistics only when evidence indicates they are inaccurate or stale.
Do not create indexes blindly from missing-index suggestions.
Do not routinely clear the complete plan cache.
Validate the solution with multiple representative parameter values.

Troubleshoot a Slow SQL Procedure