{"id":52,"date":"2024-03-14T17:38:40","date_gmt":"2024-03-14T17:38:40","guid":{"rendered":"https:\/\/twobitranch.io\/?p=52"},"modified":"2026-09-11T12:42:58","modified_gmt":"2026-09-11T12:42:58","slug":"sql-stored-procedure-performance-misery","status":"publish","type":"post","link":"https:\/\/twobitranch.io\/index.php\/2024\/03\/14\/sql-stored-procedure-performance-misery\/","title":{"rendered":"SQL Stored procedure performance misery"},"content":{"rendered":"\n

There is a special kind of sadness that comes from being told:<\/h3>\n\n\n\n
\n

“The stored procedure is slow.”<\/p>\n<\/blockquote>\n\n\n\n

At first, you remain optimistic.<\/p>\n\n\n\n

You open SQL Server Management Studio. You run the procedure. It takes 14 seconds.<\/p>\n\n\n\n

Okay. That’s not great, but 14 seconds isn’t the end of civilization.<\/p>\n\n\n\n

Then someone tells you:<\/p>\n\n\n\n

\n

“It normally takes about two minutes.”<\/p>\n<\/blockquote>\n\n\n\n

You run it again.<\/p>\n\n\n\n

This time it takes 47 seconds.<\/p>\n\n\n\n

You run it a third time.<\/p>\n\n\n\n

Three seconds.<\/p>\n\n\n\n

You stare at the screen.<\/p>\n\n\n\n

The database has decided to gaslight you.<\/p>\n\n\n\n

Welcome to the wonderful world of SQL stored procedure performance misery<\/strong>.<\/p>\n\n\n\n

\n\n\n\n

“But It Works Fine on My Machine”<\/h3>\n\n\n\n

This phrase has a SQL Server equivalent:<\/p>\n\n\n\n

\n

“It runs in SSMS.”<\/p>\n<\/blockquote>\n\n\n\n

Of course it does.<\/p>\n\n\n\n

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.<\/p>\n\n\n\n

Meanwhile, production is doing this:Application \u2193 API \u2193 Connection Pool \u2193 SQL Server \u2193 Stored Procedure \u2193 View \u2193 Another View \u2193 Another Stored Procedure \u2193 Temporary Table \u2193 Cursor \u2193 Regret<\/code><\/p>\n\n\n\n

And somebody has opened 300 concurrent requests.<\/p>\n\n\n\n

Suddenly your three-second procedure has become a 90-second procedure.<\/p>\n\n\n\n

The database isn’t broken.<\/p>\n\n\n\n

It is simply having a day.<\/p>\n\n\n\n

\n\n\n\n

The Stored Procedure That “Just Needs One More JOIN”<\/h3>\n\n\n\n

Every performance disaster begins innocently.<\/p>\n\n\n\n

Someone needs a new column.<\/p>\n\n\n\n

The developer opens the stored procedure:SELECT CustomerId, OrderDate, Total FROM Orders;<\/code><\/p>\n\n\n\n

Easy.<\/p>\n\n\n\n

Add the customer name:SELECT o.CustomerId, c.Name, o.OrderDate, o.Total FROM Orders o JOIN Customers c ON c.CustomerId = o.CustomerId;<\/code><\/p>\n\n\n\n

Done.<\/p>\n\n\n\n

Then someone needs the customer’s region.<\/p>\n\n\n\n

Another JOIN.<\/p>\n\n\n\n

Then their account manager.<\/p>\n\n\n\n

Another JOIN.<\/p>\n\n\n\n

Then the account manager’s department.<\/p>\n\n\n\n

Another JOIN.<\/p>\n\n\n\n

Then whether the customer has an active subscription.<\/p>\n\n\n\n

Another JOIN.<\/p>\n\n\n\n

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 ...<\/code><\/p>\n\n\n\n

At this point, the query isn’t fetching orders.<\/p>\n\n\n\n

It’s reconstructing civilization.<\/p>\n\n\n\n

\n\n\n\n

The Execution Plan Has Become Modern Art<\/h3>\n\n\n\n

Eventually someone says:<\/p>\n\n\n\n

\n

“Have you looked at the execution plan?”<\/p>\n<\/blockquote>\n\n\n\n

Yes.<\/p>\n\n\n\n

I have.<\/p>\n\n\n\n

The execution plan contains a large green box connected to 37 other boxes.<\/p>\n\n\n\n

There are arrows everywhere.<\/p>\n\n\n\n

Some are thick.<\/p>\n\n\n\n

Some are thin.<\/p>\n\n\n\n

One of them seems to disappear behind another operator.<\/p>\n\n\n\n

There is a warning triangle.<\/p>\n\n\n\n

You zoom in.<\/p>\n\n\n\n

You zoom out.<\/p>\n\n\n\n

You zoom in again.<\/p>\n\n\n\n

You realize you’ve been staring at it for eight minutes and still don’t know what it means.<\/p>\n\n\n\n

Then you notice:<\/p>\n\n\n\n

Table Scan.<\/strong><\/p>\n\n\n\n

On a table containing 86 million rows.<\/p>\n\n\n\n

Ah.<\/p>\n\n\n\n

There it is.<\/p>\n\n\n\n

\n\n\n\n

The Missing Index Recommendation<\/h2>\n\n\n\n

SQL Server helpfully suggests:<\/p>\n\n\n\n

\n

Missing Index: Create this index.<\/p>\n<\/blockquote>\n\n\n\n

Excellent.<\/p>\n\n\n\n

We create it.<\/p>\n\n\n\n

Performance improves dramatically.<\/p>\n\n\n\n

Everyone celebrates.<\/p>\n\n\n\n

Two months later:INSERT INTO Orders ...<\/code><\/p>\n\n\n\n

is slow.<\/p>\n\n\n\n

Updates are slow.<\/p>\n\n\n\n

Deletes are slow.<\/p>\n\n\n\n

Storage has increased.<\/p>\n\n\n\n

Index maintenance now takes 42 minutes.<\/p>\n\n\n\n

SQL Server has 19 indexes on the table.<\/p>\n\n\n\n

The original query is still slow.<\/p>\n\n\n\n

Congratulations.<\/p>\n\n\n\n

You have solved one performance problem by creating four new ones.<\/p>\n\n\n\n

\n\n\n\n

“Let’s Add NOLOCK”<\/h3>\n\n\n\n

This is where things get dangerous.<\/p>\n\n\n\n

Someone sees blocking and says:<\/p>\n\n\n\n

\n

“Just add NOLOCK.”<\/p>\n<\/blockquote>\n\n\n\n

Suddenly:FROM Orders WITH (NOLOCK)<\/code><\/p>\n\n\n\n

is everywhere.<\/p>\n\n\n\n

Why?<\/p>\n\n\n\n

Because it makes the query faster.<\/p>\n\n\n\n

Sometimes.<\/p>\n\n\n\n

It can also allow dirty reads and other concurrency anomalies.<\/p>\n\n\n\n

But those are details.<\/p>\n\n\n\n

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.<\/p>\n\n\n\n

Performance!<\/p>\n\n\n\n

\n\n\n\n

The Parameter Sniffing Horror Story<\/h3>\n\n\n\n

This is one of SQL Server’s favorite practical jokes.<\/p>\n\n\n\n

You have:CREATE PROCEDURE GetOrders @CustomerId INT AS BEGIN SELECT * FROM Orders WHERE CustomerId = @CustomerId; END<\/code><\/p>\n\n\n\n

You test it:EXEC GetOrders @CustomerId = 123;<\/code><\/p>\n\n\n\n

It’s fast.<\/p>\n\n\n\n

Production calls:EXEC GetOrders @CustomerId = 999999;<\/code><\/p>\n\n\n\n

It’s slow.<\/p>\n\n\n\n

Someone calls it with another customer.<\/p>\n\n\n\n

Fast.<\/p>\n\n\n\n

Another customer.<\/p>\n\n\n\n

Slow.<\/p>\n\n\n\n

Another.<\/p>\n\n\n\n

Fast.<\/p>\n\n\n\n

You begin to suspect the database has developed preferences.<\/p>\n\n\n\n

Then someone says:<\/p>\n\n\n\n

\n

“It’s probably parameter sniffing.”<\/p>\n<\/blockquote>\n\n\n\n

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.<\/p>\n\n\n\n

The database has essentially said:<\/p>\n\n\n\n

\n

“You gave me a parameter once. I made some decisions. Good luck.”<\/p>\n<\/blockquote>\n\n\n\n

\n\n\n\n

SELECT *<\/h3>\n\n\n\n

There is one SQL statement that has caused more long-term suffering than it deserves:SELECT *<\/code><\/p>\n\n\n\n

It starts innocently.<\/p>\n\n\n\n

\n

“We’re just returning everything.”<\/p>\n<\/blockquote>\n\n\n\n

Then the table gets a new column.<\/p>\n\n\n\n

Then another.<\/p>\n\n\n\n

Then another.<\/p>\n\n\n\n

Now the query is returning 73 columns when the application needs six.<\/p>\n\n\n\n

But nobody wants to touch the stored procedure because:<\/p>\n\n\n\n

\n

“It works.”<\/p>\n<\/blockquote>\n\n\n\n

Of course it works.<\/p>\n\n\n\n

So does carrying your entire house on your back.<\/p>\n\n\n\n

That doesn’t make it a good design.<\/p>\n\n\n\n

\n\n\n\n

The 17-Year-Old Stored Procedure<\/h3>\n\n\n\n

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<\/code><\/p>\n\n\n\n

You scroll.<\/p>\n\n\n\n

The procedure is 3,800 lines long.<\/p>\n\n\n\n

There are 14 temporary tables.<\/p>\n\n\n\n

Six nested IF<\/code> statements.<\/p>\n\n\n\n

A cursor.<\/p>\n\n\n\n

Dynamic SQL.<\/p>\n\n\n\n

Three calls to other stored procedures.<\/p>\n\n\n\n

One undocumented table.<\/p>\n\n\n\n

And a comment saying:-- This seems to fix the issue. Don't ask me why.<\/code><\/p>\n\n\n\n

You close SSMS.<\/p>\n\n\n\n

You go make coffee.<\/p>\n\n\n\n

You consider becoming a farmer.<\/p>\n\n\n\n

\n\n\n\n

Cursors: Because Sometimes We Missed the 1980s<\/h3>\n\n\n\n

There are legitimate uses for cursors.<\/p>\n\n\n\n

But sometimes you encounter:DECLARE customer_cursor CURSOR FOR SELECT CustomerId FROM Customers;<\/code><\/p>\n\n\n\n

followed by:FETCH NEXT FROM customer_cursor<\/code><\/p>\n\n\n\n

inside a loop.<\/p>\n\n\n\n

Then inside the loop:EXEC SomeStoredProcedure @CustomerId;<\/code><\/p>\n\n\n\n

And suddenly a query that could have been one set-based operation is executing 80,000 individual operations.<\/p>\n\n\n\n

Someone asks:<\/p>\n\n\n\n

\n

“Why does the report take 11 minutes?”<\/p>\n<\/blockquote>\n\n\n\n

Because we have asked SQL Server to perform 80,000 tiny tasks while wearing a blindfold.<\/p>\n\n\n\n

SQL Server is extremely good at working with sets.<\/p>\n\n\n\n

It is considerably less excited about doing the same thing one row at a time.<\/p>\n\n\n\n

\n\n\n\n

The Temp Table Empire<\/h3>\n\n\n\n

Temporary tables can be useful.<\/p>\n\n\n\n

Very useful.<\/p>\n\n\n\n

But sometimes a stored procedure creates:#Customers #Orders #FilteredOrders #ActiveCustomers #CustomerTotals #CustomerRegions #FinalResults<\/code><\/p>\n\n\n\n

Then each table is populated from the previous table.<\/p>\n\n\n\n

Eventually the procedure resembles an ETL pipeline that nobody intentionally designed.<\/p>\n\n\n\n

The query isn’t slow because SQL Server can’t execute it.<\/p>\n\n\n\n

It’s slow because we’ve built a small data-processing framework inside a stored procedure.<\/p>\n\n\n\n

\n\n\n\n

Dynamic SQL: The Final Boss<\/h3>\n\n\n\n

Then you encounter:SET @sql = ' SELECT ... FROM ... WHERE ' + @whereClause;<\/code><\/p>\n\n\n\n

You know immediately that you’re going to have a long afternoon.<\/p>\n\n\n\n

Dynamic SQL isn’t inherently bad.<\/p>\n\n\n\n

Sometimes it is exactly the right tool.<\/p>\n\n\n\n

But when a stored procedure dynamically constructs half the query, debugging becomes an adventure.<\/p>\n\n\n\n

You end up doing:PRINT @sql;<\/code><\/p>\n\n\n\n

Then copying the output into another query window.<\/p>\n\n\n\n

Then fixing the missing quote.<\/p>\n\n\n\n

Then realizing the parameter isn’t there.<\/p>\n\n\n\n

Then adding another PRINT<\/code>.<\/p>\n\n\n\n

Eventually you have reconstructed the query manually.<\/p>\n\n\n\n

At this point you are no longer debugging SQL.<\/p>\n\n\n\n

You’re reverse-engineering a compiler.<\/p>\n\n\n\n

\n\n\n\n

The .NET Developer’s Contribution<\/h3>\n\n\n\n

ASP.NET Core developers aren’t innocent here.<\/p>\n\n\n\n

We can absolutely make SQL performance worse from the application layer.<\/p>\n\n\n\n

For example:foreach (var customer in customers) { await repository.GetOrdersAsync(customer.Id); }<\/code><\/p>\n\n\n\n

Congratulations.<\/p>\n\n\n\n

You have invented the N+1 query problem<\/strong>.<\/p>\n\n\n\n

If there are 10,000 customers, congratulations again.<\/p>\n\n\n\n

You now have approximately 10,001 database calls.<\/p>\n\n\n\n

The stored procedure may be perfectly optimized.<\/p>\n\n\n\n

The application has simply decided to call it 10,000 times.<\/p>\n\n\n\n

Sometimes the SQL isn’t the problem.<\/p>\n\n\n\n

Sometimes the problem is the person calling SQL.<\/p>\n\n\n\n

And sometimes that person is us.<\/p>\n\n\n\n

\n\n\n\n

The Repository That Hides Everything<\/h3>\n\n\n\n

Another classic architecture looks like:Controller \u2193 Service \u2193 Repository \u2193 Stored Procedure \u2193 View \u2193 Another View \u2193 Table<\/code><\/p>\n\n\n\n

The developer sees:await _repository.GetOrdersAsync();<\/code><\/p>\n\n\n\n

and assumes the operation is simple.<\/p>\n\n\n\n

But underneath:GetOrdersAsync() \u2193 GetOrders \u2193 GetCustomerOrders \u2193 GetCustomerOrderDetails \u2193 vw_OrderDetails \u2193 vw_CustomerDetails \u2193 Orders<\/code><\/p>\n\n\n\n

The abstraction has successfully hidden the problem.<\/p>\n\n\n\n

Unfortunately, it has also hidden the reason the problem exists.<\/p>\n\n\n\n

Abstraction is useful.<\/p>\n\n\n\n

Too much abstraction can make performance debugging significantly harder.<\/strong><\/p>\n\n\n\n

\n\n\n\n

Measure Before You Optimize<\/h3>\n\n\n\n

The most important performance rule is also the least exciting:<\/p>\n\n\n\n

\n

Measure first.<\/strong><\/p>\n<\/blockquote>\n\n\n\n

Don’t rewrite a stored procedure because it looks ugly.<\/p>\n\n\n\n

Don’t add an index because someone suggested it.<\/p>\n\n\n\n

Don’t add NOLOCK<\/code>.<\/p>\n\n\n\n

Don’t change joins randomly.<\/p>\n\n\n\n

Don’t introduce five temporary tables because you think they might be faster.<\/p>\n\n\n\n

Find out where the time is actually going.<\/p>\n\n\n\n

Look at things such as:<\/p>\n\n\n\n

    \n
  • Actual execution plans<\/li>\n\n\n\n
  • Logical reads<\/li>\n\n\n\n
  • CPU time<\/li>\n\n\n\n
  • Elapsed time<\/li>\n\n\n\n
  • Wait statistics<\/li>\n\n\n\n
  • Blocking<\/li>\n\n\n\n
  • Query duration<\/li>\n\n\n\n
  • Cardinality estimates<\/li>\n\n\n\n
  • Index usage<\/li>\n\n\n\n
  • Statistics<\/li>\n\n\n\n
  • Parameter behavior<\/li>\n\n\n\n
  • Application-generated SQL<\/li>\n\n\n\n
  • Connection pool behavior<\/li>\n<\/ul>\n\n\n\n

    A query that takes five seconds may not be a SQL problem.<\/p>\n\n\n\n

    It might be waiting 4.8 seconds for a lock.<\/p>\n\n\n\n

    Or waiting for a connection.<\/p>\n\n\n\n

    Or waiting on another query.<\/p>\n\n\n\n

    Or transferring a massive result set across the network.<\/p>\n\n\n\n

    Or being executed 500 times.<\/p>\n\n\n\n

    The stopwatch alone doesn’t tell you the whole story.<\/p>\n\n\n\n

    \n\n\n\n

    Sometimes the Stored Procedure Isn’t the Problem<\/h3>\n\n\n\n

    This is perhaps the most important lesson.<\/p>\n\n\n\n

    Performance exists across the entire stack.Browser \u2193 ASP.NET Core \u2193 Application Service \u2193 Repository \u2193 Database Driver \u2193 Connection Pool \u2193 SQL Server \u2193 Stored Procedure \u2193 Tables \/ Indexes \/ Storage<\/code><\/p>\n\n\n\n

    A slow request doesn’t automatically mean:Stored Procedure = Bad<\/code><\/p>\n\n\n\n

    It might be:Stored Procedure = 200ms Network = 50ms Application processing = 300ms Serialization = 100ms Waiting for connection = 2 seconds<\/code><\/p>\n\n\n\n

    Now you’ve spent three days optimizing a 200ms query.<\/p>\n\n\n\n

    Excellent work.<\/p>\n\n\n\n

    Unfortunately, the user is still waiting 2.6 seconds.<\/p>\n\n\n\n

    \n\n\n\n

    The Best Stored Procedure Is Sometimes No Stored Procedure<\/h3>\n\n\n\n

    This is not a declaration that stored procedures are bad.<\/p>\n\n\n\n

    They’re not.<\/p>\n\n\n\n

    Stored procedures can be excellent for:<\/p>\n\n\n\n