There is a special kind of sadness that comes from being told:
“The stored procedure is slow.”
At first, you remain optimistic.
You open SQL Server Management Studio. You run the procedure. It takes 14 seconds.
Okay. That’s not great, but 14 seconds isn’t the end of civilization.
Then someone tells you:
“It normally takes about two minutes.”
You run it again.
This time it takes 47 seconds.
You run it a third time.
Three seconds.
You stare at the screen.
The database has decided to gaslight you.
Welcome to the wonderful world of SQL stored procedure performance misery.
“But It Works Fine on My Machine”
This phrase has a SQL Server equivalent:
“It runs in SSMS.”
Of course it does.
You’re testing it manually with a carefully selected set of parameters, no application traffic, no concurrent users, no blocking, and probably immediately after SQL Server decided to give you the execution plan you wanted.
Meanwhile, production is doing this:Application ↓ API ↓ Connection Pool ↓ SQL Server ↓ Stored Procedure ↓ View ↓ Another View ↓ Another Stored Procedure ↓ Temporary Table ↓ Cursor ↓ Regret
And somebody has opened 300 concurrent requests.
Suddenly your three-second procedure has become a 90-second procedure.
The database isn’t broken.
It is simply having a day.
The Stored Procedure That “Just Needs One More JOIN”
Every performance disaster begins innocently.
Someone needs a new column.
The developer opens the stored procedure:SELECT CustomerId, OrderDate, Total FROM Orders;
Easy.
Add the customer name:SELECT o.CustomerId, c.Name, o.OrderDate, o.Total FROM Orders o JOIN Customers c ON c.CustomerId = o.CustomerId;
Done.
Then someone needs the customer’s region.
Another JOIN.
Then their account manager.
Another JOIN.
Then the account manager’s department.
Another JOIN.
Then whether the customer has an active subscription.
Another JOIN.
Eventually:FROM Orders o JOIN Customers c JOIN Addresses a JOIN Regions r JOIN AccountManagers am JOIN Departments d JOIN Subscriptions s JOIN SubscriptionPlans sp JOIN PaymentMethods pm JOIN ...
At this point, the query isn’t fetching orders.
It’s reconstructing civilization.
The Execution Plan Has Become Modern Art
Eventually someone says:
“Have you looked at the execution plan?”
Yes.
I have.
The execution plan contains a large green box connected to 37 other boxes.
There are arrows everywhere.
Some are thick.
Some are thin.
One of them seems to disappear behind another operator.
There is a warning triangle.
You zoom in.
You zoom out.
You zoom in again.
You realize you’ve been staring at it for eight minutes and still don’t know what it means.
Then you notice:
Table Scan.
On a table containing 86 million rows.
Ah.
There it is.
The Missing Index Recommendation
SQL Server helpfully suggests:
Missing Index: Create this index.
Excellent.
We create it.
Performance improves dramatically.
Everyone celebrates.
Two months later:INSERT INTO Orders ...
is slow.
Updates are slow.
Deletes are slow.
Storage has increased.
Index maintenance now takes 42 minutes.
SQL Server has 19 indexes on the table.
The original query is still slow.
Congratulations.
You have solved one performance problem by creating four new ones.
“Let’s Add NOLOCK”
This is where things get dangerous.
Someone sees blocking and says:
“Just add NOLOCK.”
Suddenly:FROM Orders WITH (NOLOCK)
is everywhere.
Why?
Because it makes the query faster.
Sometimes.
It can also allow dirty reads and other concurrency anomalies.
But those are details.
The important thing is that the report now runs in 1.2 seconds and occasionally tells the finance department that an order exists when it doesn’t.
Performance!
The Parameter Sniffing Horror Story
This is one of SQL Server’s favorite practical jokes.
You have:CREATE PROCEDURE GetOrders @CustomerId INT AS BEGIN SELECT * FROM Orders WHERE CustomerId = @CustomerId; END
You test it:EXEC GetOrders @CustomerId = 123;
It’s fast.
Production calls:EXEC GetOrders @CustomerId = 999999;
It’s slow.
Someone calls it with another customer.
Fast.
Another customer.
Slow.
Another.
Fast.
You begin to suspect the database has developed preferences.
Then someone says:
“It’s probably parameter sniffing.”
And now you are 14 browser tabs deep into articles explaining query plans, cardinality estimates, statistics, recompilation, and why SQL Server remembers things you didn’t ask it to remember.
The database has essentially said:
“You gave me a parameter once. I made some decisions. Good luck.”
SELECT *
There is one SQL statement that has caused more long-term suffering than it deserves:SELECT *
It starts innocently.
“We’re just returning everything.”
Then the table gets a new column.
Then another.
Then another.
Now the query is returning 73 columns when the application needs six.
But nobody wants to touch the stored procedure because:
“It works.”
Of course it works.
So does carrying your entire house on your back.
That doesn’t make it a good design.
The 17-Year-Old Stored Procedure
You open a stored procedure and see:-- Added by Bob - 2008 -- Added by Mike - 2011 -- Temporary fix - 2014 -- Performance fix - 2016 -- DO NOT REMOVE - 2017 -- Added for new reporting requirements - 2019 -- Hotfix - 2021 -- Temporary workaround - 2022 -- Added for customer request - 2024
You scroll.
The procedure is 3,800 lines long.
There are 14 temporary tables.
Six nested IF statements.
A cursor.
Dynamic SQL.
Three calls to other stored procedures.
One undocumented table.
And a comment saying:-- This seems to fix the issue. Don't ask me why.
You close SSMS.
You go make coffee.
You consider becoming a farmer.
Cursors: Because Sometimes We Missed the 1980s
There are legitimate uses for cursors.
But sometimes you encounter:DECLARE customer_cursor CURSOR FOR SELECT CustomerId FROM Customers;
followed by:FETCH NEXT FROM customer_cursor
inside a loop.
Then inside the loop:EXEC SomeStoredProcedure @CustomerId;
And suddenly a query that could have been one set-based operation is executing 80,000 individual operations.
Someone asks:
“Why does the report take 11 minutes?”
Because we have asked SQL Server to perform 80,000 tiny tasks while wearing a blindfold.
SQL Server is extremely good at working with sets.
It is considerably less excited about doing the same thing one row at a time.
The Temp Table Empire
Temporary tables can be useful.
Very useful.
But sometimes a stored procedure creates:#Customers #Orders #FilteredOrders #ActiveCustomers #CustomerTotals #CustomerRegions #FinalResults
Then each table is populated from the previous table.
Eventually the procedure resembles an ETL pipeline that nobody intentionally designed.
The query isn’t slow because SQL Server can’t execute it.
It’s slow because we’ve built a small data-processing framework inside a stored procedure.
Dynamic SQL: The Final Boss
Then you encounter:SET @sql = ' SELECT ... FROM ... WHERE ' + @whereClause;
You know immediately that you’re going to have a long afternoon.
Dynamic SQL isn’t inherently bad.
Sometimes it is exactly the right tool.
But when a stored procedure dynamically constructs half the query, debugging becomes an adventure.
You end up doing:PRINT @sql;
Then copying the output into another query window.
Then fixing the missing quote.
Then realizing the parameter isn’t there.
Then adding another PRINT.
Eventually you have reconstructed the query manually.
At this point you are no longer debugging SQL.
You’re reverse-engineering a compiler.
The .NET Developer’s Contribution
ASP.NET Core developers aren’t innocent here.
We can absolutely make SQL performance worse from the application layer.
For example:foreach (var customer in customers) { await repository.GetOrdersAsync(customer.Id); }
Congratulations.
You have invented the N+1 query problem.
If there are 10,000 customers, congratulations again.
You now have approximately 10,001 database calls.
The stored procedure may be perfectly optimized.
The application has simply decided to call it 10,000 times.
Sometimes the SQL isn’t the problem.
Sometimes the problem is the person calling SQL.
And sometimes that person is us.
The Repository That Hides Everything
Another classic architecture looks like:Controller ↓ Service ↓ Repository ↓ Stored Procedure ↓ View ↓ Another View ↓ Table
The developer sees:await _repository.GetOrdersAsync();
and assumes the operation is simple.
But underneath:GetOrdersAsync() ↓ GetOrders ↓ GetCustomerOrders ↓ GetCustomerOrderDetails ↓ vw_OrderDetails ↓ vw_CustomerDetails ↓ Orders
The abstraction has successfully hidden the problem.
Unfortunately, it has also hidden the reason the problem exists.
Abstraction is useful.
Too much abstraction can make performance debugging significantly harder.
Measure Before You Optimize
The most important performance rule is also the least exciting:
Measure first.
Don’t rewrite a stored procedure because it looks ugly.
Don’t add an index because someone suggested it.
Don’t add NOLOCK.
Don’t change joins randomly.
Don’t introduce five temporary tables because you think they might be faster.
Find out where the time is actually going.
Look at things such as:
- Actual execution plans
- Logical reads
- CPU time
- Elapsed time
- Wait statistics
- Blocking
- Query duration
- Cardinality estimates
- Index usage
- Statistics
- Parameter behavior
- Application-generated SQL
- Connection pool behavior
A query that takes five seconds may not be a SQL problem.
It might be waiting 4.8 seconds for a lock.
Or waiting for a connection.
Or waiting on another query.
Or transferring a massive result set across the network.
Or being executed 500 times.
The stopwatch alone doesn’t tell you the whole story.
Sometimes the Stored Procedure Isn’t the Problem
This is perhaps the most important lesson.
Performance exists across the entire stack.Browser ↓ ASP.NET Core ↓ Application Service ↓ Repository ↓ Database Driver ↓ Connection Pool ↓ SQL Server ↓ Stored Procedure ↓ Tables / Indexes / Storage
A slow request doesn’t automatically mean:Stored Procedure = Bad
It might be:Stored Procedure = 200ms Network = 50ms Application processing = 300ms Serialization = 100ms Waiting for connection = 2 seconds
Now you’ve spent three days optimizing a 200ms query.
Excellent work.
Unfortunately, the user is still waiting 2.6 seconds.
The Best Stored Procedure Is Sometimes No Stored Procedure
This is not a declaration that stored procedures are bad.
They’re not.
Stored procedures can be excellent for:
- Complex reporting
- Database-heavy operations
- Security boundaries
- Batch operations
- Data-intensive transformations
- Encapsulating database-specific logic
- Operations where reducing network round trips matters
But they aren’t automatically faster simply because they are stored procedures.
A poorly designed stored procedure is still a poorly designed query.
Putting bad SQL inside a stored procedure doesn’t transform it into good SQL.
It just gives the bad SQL a name.
So What Should We Actually Do?
When a stored procedure becomes slow, resist the urge to immediately rewrite everything.
Start with evidence.
Ask:
- How slow is it?
- How often is it executed?
- Which parameters cause the problem?
- What does the actual execution plan show?
- Are estimates wildly different from actual row counts?
- Are there missing or inappropriate indexes?
- Are statistics current?
- Is the query being blocked?
- Is parameter sniffing involved?
- Is the application calling it unnecessarily many times?
- Is it returning far more data than necessary?
- Would changing the data access pattern solve the problem more effectively?
Only after answering those questions should you start changing things.
And Then Someone Says, “Can We Just Add an Index?”
Of course they do.
They always do.
It’s practically a law of software development.
“The query is slow.”
“Add an index.”
“The API is slow.”
“Add caching.”
“The application is slow.”
“Scale it.”
“The report is slow.”
“Run it at night.”
These suggestions aren’t necessarily wrong.
They’re just not diagnoses.
Performance engineering isn’t about collecting optimization tricks.
It’s about understanding why the system is slow.
The Real Misery
The real misery of SQL stored procedures isn’t that SQL Server is complicated.
It’s that databases are often where years of application decisions eventually meet.
A stored procedure might contain:
- 15 years of business rules
- 7 developers’ assumptions
- 4 reporting requirements
- 3 emergency fixes
- 2 abandoned features
- 1 mysterious cursor
And one innocent developer asking:
“Can we just add a column?”
That is how a 30-line query becomes a 3,000-line stored procedure.
Final Thoughts
Stored procedures aren’t evil.
Indexes aren’t magic.
NOLOCK isn’t a performance button.
SELECT * isn’t free.
Cursors aren’t automatically forbidden.
Temporary tables aren’t automatically bad.
Dynamic SQL isn’t automatically bad.
ORMs aren’t automatically slow.
And adding an interface won’t make your database faster.
The uncomfortable truth is that database performance is a system problem.
The SQL matters.
The schema matters.
The indexes matter.
The execution plan matters.
The application matters.
The way the application calls the database matters.
And sometimes the biggest performance improvement isn’t making the stored procedure execute 20% faster.
It’s discovering that you didn’t need to execute it 10,000 times.
So the next time someone says:
“The stored procedure is slow.”
Don’t panic.
Don’t immediately add an index.
Don’t add NOLOCK.
Don’t blame Entity Framework.
Don’t rewrite 4,000 lines of SQL.
Take a breath.
Look at the execution plan.
Measure the actual behavior.
Find the bottleneck.
And remember:
SQL Server isn’t necessarily having a performance problem.
It may simply be faithfully executing everything we’ve spent the last fifteen years asking it to do.