From Zero to Hero: Execution Plans in Two Hours

A slow dropdown, a permission error, and a crash course in execution plans - all before lunch.

The Problem

We had a slow dropdown. One of those "loads after everything else" situations - the page would render fine, then you'd wait... and wait... for a single dropdown to populate.

We discovered the latency after a production release, and I was asked to look into it. I dug in and identified the query behind the dropdown - it had some gnarly OR conditions, a string concatenation in the ORDER BY, and was being hit by users with hundreds of filter values in their payload. One user from Texas had over a thousand, since everything's bigger in Texas.

I knew the shape of the problem - complex WHERE clause, potential index issues - but I didn't know how to prove it.

What I Did

Our new CTO asked if we'd run an EXPLAIN on the query. She suggested we check for full table scans.

I had no idea what that meant. I used AI to understand what an execution plan actually was and how to run one in SQL Server - SHOWPLAN, it turns out, is SQL Server's way of showing you exactly what the database is doing under the hood.

So I tried to run it.

SHOWPLAN permission denied in database

Great.

The Permission Wall

I pasted the error in our team chat. Our DevOps guy was already on it - he'd been watching the thread in real time.

Turns out devs didn't have SHOWPLAN access in any environment. He started digging and realized our whole permission structure for SQL Server was a mess - individual users instead of groups, no granular roles, onboarding gaps everywhere.

He spent the next hour cleaning it up, creating proper groups, and getting devs the access we needed. Infrastructure debt, surfaced by one slow query.

The Execution Plan

Once I had access, I ran the query with the actual execution plan enabled. Here's what I saw:

  • 29% - Clustered Index Scan (effectively a full table scan)
  • 69% - Sorting

That's 98% of the query cost in two operations. The sorting was killing us because we were ordering by a concatenated string:

ORDER BY [c].[FirstName] + N' ' + [c].[LastName]

I didn't fully understand what I was looking at, so I fed the execution plan to AI. It broke down what each operation meant and confirmed: the concatenation was forcing a full sort in memory, and the OR conditions in the WHERE clause were preventing efficient index seeks.

The Fix

Our solutions architect helped me think through the remediation. We landed on:

  1. Remove the concatenation - use .OrderBy(FirstName).ThenBy(LastName) instead
  2. Add a composite index on those two fields
  3. Investigate why the clustered index is scanning instead of seeking (likely the null checks)

I suggested the investigation approach: generate the raw SQL from C# using ToQueryStringAsync(), make adjustments, and run SHOWPLAN on each variation. That way we can see exactly how each change affects the execution plan before we commit to anything.

I orchestrated the ticket creation, documented the root cause, and captured the investigation plan so nothing got lost.

The Takeaway

The CTO put it well when talking about query optimization: "It's a bit of a lost art given the pervasiveness of ORMs."

Entity Framework makes it easy to never think about what's actually happening at the database level. You write LINQ, it generates SQL, and you trust it works. Until it doesn't.

I went from not knowing what an execution plan was to identifying root cause and filing a detailed optimization ticket - in about two hours. AI compressed the learning curve. It didn't replace the understanding; it got me to a testable hypothesis fast enough to be useful in the moment.

Next time you have a slow query, don't just throw indexes at it. Look at the execution plan. It'll tell you exactly where the time is going.

Back to All Posts