How to Optimize Slow SQL Server Stored Procedures

Key Takeaways: 

• Measure performance and locate actual time bottlenecks using system views or Query Store before making code changes. 
• Analyze actual execution plans and stats to guide effective optimization rather than relying on estimated query costs. 
• Structure queries with SARGable predicates and set-based logic while retrieving only essential data columns to maximize efficiency. 
• Utilize modern database features like parameter-sensitive plan optimization and keep transactions short to maintain smooth processing.

Even without a single code revision, a stored procedure that ran smoothly for months or years can unexpectedly slow down. When this happens, the logical starting point is to ask: What changed?

Maybe SQL Server picked a different execution plan. The procedure might now be operating on a different data distribution, blocking might have occurred, or the server load might just be different than before.  So we don't go and immediately rewrite the stored procedure, add indexes, or apply hints. First we try to understand what is going on and where time is really spent. 

It's a systematic way that we use in our DBA support work to troubleshoot SQL Server performance issues without making unnecessary changes. 

Here are 10 performance tuning techniques that come to our mind when we are troubleshooting slow stored procedures in SQL Server.

1. Find out where your time is really going 

Don't change things you haven't measured. 

When we get a report that a stored procedure is slow, the first thing we want to know is where the time is being spent. Is it CPU, IO, blocking, waiting, compilation or some statement in the procedure? 

One of the first places we check is sys.dm_exec_query_stats, that can be used to find expensive queries based on their execution stats: 

SELECT 
	qs.execution_count, 
	qs.total_worker_time / 1000 AS total_cpu_ms, 
	qs.total_elapsed_time / 1000 AS total_elapsed_ms, 
	qs.total_logical_reads, 
	qs.total_logical_writes, 
	SUBSTRING( 
    	st.text, 
    	(qs.statement_start_offset / 2) + 1, 
    	( 
        	(CASE qs.statement_end_offset 
            	WHEN -1 THEN DATALENGTH(st.text) 
            	ELSE qs.statement_end_offset 
         	END - qs.statement_start_offset) / 2 
    	) + 1 
	) AS query_text 
FROM sys.dm_exec_query_stats AS qs 
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st 
ORDER BY qs.total_elapsed_time DESC; 

If the problem occurs at this very moment, then we also check the requests that are running right now:

SELECT 
	session_id, 
	status, 
	command, 
	wait_type, 
	wait_time, 
	blocking_session_id, 
	cpu_time, 
	total_elapsed_time 
FROM sys.dm_exec_requests 
WHERE session_id <> @@SPID; 

We look at the wait type, blocking session, CPU time and elapsed time. These can tell us quickly if we are looking at a query problem or a query that’s just waiting for something else. 

If the Query Store is available, we can use it to look further back in time. When a procedure was fast last week but is slow today, we can use Query Store to compare plans and runtime statistics. 

Then we can ask: 

• Is the execution plan changed? 
• Execution time increased suddenly? 
• Did the CPU or logical reads go up? 
• Is one plan a lot worse than the other? 
• Is the problem after a deployment or data change? 

This is typically where we start our SQL Server query performance tuning investigation. 

2. View the Actual Execution Plan 

Once we know which statement or procedure is causing the problem, we look at the actual execution plan. 

The execution plan gives us insight into how SQL Server is actually executing the query. Some of the things we look for include:

• Table scans or index scans where a seek may have been expected 
• Large differences between estimated and actual row counts 
• Expensive joins 
• Sorts 
• Key lookups 
• Large memory grants 
• Implicit conversions 
• Missing or ineffective indexes 
• Spills to tempdb 
• Operators processing far more rows than expected

For example, if SQL Server estimates that a query will return 10 rows but actually processes 500,000 rows, that difference immediately gets our attention. 

It can be out-of-date stats, data distribution issues, parameter sensitivity, or a cardinality estimation problem. 

One of the things we try not to do is look at the operator that has the highest percentage of estimated cost and assume that it is automatically the root cause. 

An operator showing 80% of the estimated cost does not necessarily mean that changing it will fix the real performance problem. We prefer to look at the execution plan as a whole and understand how the operators work together. 

3. Check for Parameter Sniffing and Parameter-Sensitive Plans 

This is especially important when a stored procedure is fast for one parameter value but slow for another. 

For example:

CREATE PROCEDURE GetCustomerOrders 
	@CustomerID INT 
AS 
BEGIN 
	SELECT * 
	FROM dbo.Orders 
	WHERE CustomerID = @CustomerID; 
END 

Imagine that one customer has 20 orders while another customer has 20 million orders. 

The best execution strategy may not be the same for both customers. 

SQL Server can compile a plan using the parameter value available during compilation, and that plan may not perform equally well for other parameter values. This is commonly referred to as parameter sniffing. 

When we see this type of behavior, we don't immediately assume that parameter sniffing is bad. We first want to understand why the plans behave differently. 

Before reaching for: 

OPTION (RECOMPILE) 

we check the execution plans, parameter values, and data distribution. 

Modern SQL Server versions also provide Parameter Sensitive Plan optimization (PSP) for certain parameter-sensitive queries. PSP can allow multiple plan variants for different parameter value ranges. 

PSP is associated with SQL Server 2022 and database compatibility level 160, so we always check the SQL Server version and compatibility level before deciding how to handle parameter sensitivity. 

The important question is not simply: 

"Is parameter sniffing bad?" 

The better question is: 

"Is the cached plan appropriate for the range of parameter values this procedure receives?"

4. Make Predicates SARGable 

SARGability is another area we check when reviewing a slow query. 

A SARGable predicate allows SQL Server to use an index efficiently when an appropriate index exists. 

For example, this can make efficient index access more difficult because a function is being applied to the column:

WHERE YEAR(OrderDate) = 2026

A range-based predicate is usually a better approach:

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

We also look for patterns such as: 

WHERE CustomerName LIKE '%John%' 

A leading wildcard generally prevents SQL Server from using a normal B-tree index for an efficient seek on that column. 

Other things we check include:

• Functions applied to indexed columns 
• Implicit data-type conversions 
• Expressions that prevent efficient index access 
• Leading wildcards 
• Predicates that do not match the available indexing strategy 

We don't treat SARGability as a simple checklist where every function automatically means a query is bad. We look at the actual execution plan and the data involved before deciding whether it is really a performance issue. 

5. Remove Work the Query Doesn't Actually Need 

One of the simplest improvements we look for is unnecessary work. 

For example:

SELECT * 
FROM dbo.CustomerOrders; 

may return many more columns than the application actually needs. 

Instead, we prefer selecting only the required columns: 

SELECT 
	OrderID, 
	CustomerID, 
	OrderDate, 
	OrderStatus 
FROM dbo.CustomerOrders; 

This can reduce unnecessary data processing and network traffic. It can also make it easier to design an appropriate covering index. 

The same idea applies to joins. 

If we only need to check whether a related row exists, EXISTS often communicates that requirement more clearly:

SELECT c.CustomerID 
FROM dbo.Customers AS c 
WHERE EXISTS 
( 
	SELECT 1 
	FROM dbo.Orders AS o 
	WHERE o.CustomerID = c.CustomerID 
); 

EXISTS tells SQL Server that we are interested in whether a matching row exists. Once that existence is established, SQL Server can often stop looking for additional matching rows. 

We generally avoid SELECT * in stored procedures unless there is a specific reason to use it.

6. Prefer Set-Based Logic Over Row-by-Row Processing 

Whenever we see a cursor or repeated row-by-row processing inside a stored procedure, we ask whether the same operation can be expressed using set-based logic. 

For example, processing rows individually may look like this:

DECLARE @CustomerID INT; 
 
DECLARE customer_cursor CURSOR FOR 
SELECT CustomerID 
FROM dbo.Customers; 
 
OPEN customer_cursor; 
 
FETCH NEXT FROM customer_cursor INTO @CustomerID; 
 
WHILE @@FETCH_STATUS = 0 
BEGIN 
	-- Process one customer 
 
	FETCH NEXT FROM customer_cursor INTO @CustomerID; 
END; 
 
CLOSE customer_cursor; 
DEALLOCATE customer_cursor;

A cursor is not automatically wrong. There are situations where it is genuinely appropriate. 

But in many cases, we can express the same business operation using a single UPDATE, INSERT, DELETE, or other set-based statement. 

For example: 

For example: 
UPDATE c 
SET c.CustomerStatus = 'Active' 
FROM dbo.Customers AS c 
WHERE EXISTS 
( 
	SELECT 1 
	FROM dbo.Orders AS o 
	WHERE o.CustomerID = c.CustomerID 
); 

We don't remove a cursor simply because it is a cursor. We first understand what the code is doing and why it was written that way. 

Where the same work can safely be performed as a set, that is usually where we look for the performance improvement.

7. Choose Between Temporary Tables and Table Variables Carefully 

This is one area where older SQL Server tuning advice can become misleading. 

A common rule is to never use table variables for large amounts of data. We don't treat that as a blanket rule. 

When we decide between a temporary table and a table variable, we consider: 

• Number of rows 
• Indexing requirements 
• Query complexity 
• Statistics 
• SQL Server version 
• Database compatibility level 
• How the intermediate result is being used

For example:

CREATE TABLE #CustomerOrders 
( 
	CustomerID INT, 
	OrderCount INT 
); 

Temporary tables can have indexes and statistics, which can help SQL Server make better cardinality estimates for subsequent queries. 

Modern SQL Server also includes Table Variable Deferred Compilation (TVDC) with the appropriate compatibility level. This improves how table variables are compiled by delaying compilation until the first execution, after the table variable has been populated. 

Because of this, the older advice that table variables always behave as though they contain one row is no longer accurate for modern environments. 

If we find a table variable inside a slow procedure, we don't automatically replace it. We first look at the workload, row counts, execution plan, and SQL Server version. 

8. Be Careful With Dynamic SQL 

Dynamic SQL is not automatically bad. The problem is usually how it is constructed. 

For example, we would avoid building SQL like this:

SET @sql = 'SELECT * FROM dbo.Customers 
        	WHERE CustomerName = ''' + @CustomerName + ''''; 
 
EXEC(@sql); 

A parameterized approach is safer and generally better: 

DECLARE @sql NVARCHAR(MAX); 
 
SET @sql = N' 
	SELECT CustomerID, CustomerName 
	FROM dbo.Customers 
	WHERE CustomerName = @CustomerName;'; 
 
EXEC sys.sp_executesql 
	@sql, 
	N'@CustomerName NVARCHAR(100)', 
	@CustomerName = @CustomerName; 

Using sp_executesql allows values to be passed as parameters instead of concatenating them directly into the SQL string. 

This improves safety and can also improve plan reuse. 

We also pay attention to the data types and lengths of parameters used in dynamic SQL. If the parameter type does not match the underlying column type, implicit conversions can occur and affect performance. 

So when we review dynamic SQL, we look at both how the SQL is constructed and how the parameters are defined. 

9. Keep Transactions Short and Investigate Blocking 

Sometimes a stored procedure is slow not because the query itself is inefficient, but because it is waiting. 

When a procedure suddenly takes much longer than normal, we check waits and blocking. 

For example: 

SELECT 
	session_id, 
	status, 
	wait_type, 
	wait_time, 
	blocking_session_id, 
	total_elapsed_time 
FROM sys.dm_exec_requests 
WHERE session_id <> @@SPID; 

If we find a blocking session, we then investigate what that session is doing and why its transaction has remained open. 

Long-running transactions can hold locks for longer periods and increase blocking. Depending on the workload, they can also increase transaction-log pressure. 

For example:

BEGIN TRANSACTION; 
 
UPDATE dbo.CustomerOrders 
SET OrderStatus = 'Processed' 
WHERE CustomerID = @CustomerID; 
 
-- Keep the transaction scope focused 
 
COMMIT TRANSACTION; 

We try to keep the transaction around the actual unit of work rather than performing unrelated processing inside it. 

Blocking does not automatically mean that the query is bad. Sometimes the query is perfectly reasonable but is simply waiting behind another transaction. 

That distinction matters when troubleshooting performance. 

10. Use Query Store and Modern SQL Server Features Before Making Permanent Changes 

Query Store is one of the tools we rely on when we need to understand how query performance has changed over time. 

Unlike looking only at what is happening right now, Query Store allows us to compare performance history, execution plans, and runtime statistics. 

If a stored procedure suddenly becomes slow after running normally for months, we can use Query Store to check whether the execution plan changed. 

Depending on the environment, Query Store provides capabilities such as:

• Plan forcing 
• Query Store hints 
• Query performance history 
• Runtime statistics 
• Plan comparisons 
• Regression analysis

Query Store hints can be useful when we need to influence query behavior without immediately changing application code. 

Automatic plan correction is another feature worth knowing about. In supported environments, SQL Server can use Query Store information to identify certain plan regressions and force a previously known good plan. 

Modern SQL Server also includes several Intelligent Query Processing capabilities, depending on the SQL Server version and database compatibility level, including: 

• Table Variable Deferred Compilation 
• Scalar UDF Inlining 
• Memory Grant Feedback 
• Parameter Sensitive Plan optimization 
• Other query-processing feedback capabilities 

This is why we always check the SQL Server version and database compatibility level before applying an old tuning trick from a blog post or forum answer. 

The right tuning approach for SQL Server 2016 may not be the same as the right approach for SQL Server 2022 or newer versions.

Conclusion:

To optimize slow SQL Server stored procedures, you don’t need all ten methods on every procedure. Most slow procedures have one or two real problems, such as a missing index, an outdated statistic, a bad parameter-sensitive plan, or a blocking session, and fixing those gives you most of the gain.

So start with a baseline. Check the execution plan and wait stats, change one thing, and run the test again. If the numbers don’t improve, undo the change and move on to the next suspect. That keeps your fixes backed by evidence and stops you from rewriting queries that were never the problem.

If a stored procedure keeps slowing down and you can’t pin down why, Geopits’ DBA team can review it with you and help bring it back to normal speed.

Frequently Asked Questions 

Why is my SQL query running so slow?

Your SQL query is running slow usually because of missing indexes, full table scans, stale statistics, or database blocking. Review your execution plan to spot the exact bottleneck. 

How do we optimize a slow stored procedure in SQL Server? 

We start by identifying where the time is being spent. We check the actual execution plan, Query Store, CPU, logical reads, waits, blocking, statistics, and parameter sensitivity before changing the procedure. 

What is the first thing to check when a stored procedure becomes slow? 

We first try to determine whether the problem is caused by the query itself or by something around it, such as blocking, waits, a changed execution plan, data growth, or a change in workload. 

How to optimize a slow running SQL query? 

To optimize a slow SQL query, index frequently searched columns, avoid SELECT * by specifying columns, use EXISTS instead of IN, optimize JOIN conditions, and analyze the execution plan to identify and eliminate bottlenecks or full table scans.

Should we always use temporary tables instead of table variables? 

No. The right choice depends on the workload, row counts, query complexity, SQL Server version, and compatibility level. Modern SQL Server also includes Table Variable Deferred Compilation. 

Does EXISTS always perform better than COUNT(*)? 

Not always. They express different intentions. When the requirement is simply to determine whether a matching row exists, EXISTS communicates that intent and can allow SQL Server to stop once a qualifying row is found. 

What is the best tool for SQL Server performance troubleshooting? 

There isn't one tool for every situation. We commonly use execution plans, Query Store, DMVs such as sys.dm_exec_requests and sys.dm_exec_query_stats, and wait and blocking information together.

Boost your Database performance with our performance-tuning experts today!

We explored the various ways to boost the performance of a stored procedure. In general, as application developers or business analysts, you should not worry about optimization and performance tuning, and leave the job to performance-tuning experts.

Further reading:

• Top 5 Cloud Databases of 2023
• Important aspects of database security
• How to Manage a Large Database in SQL Server