Performance problems do not always arrive with an alert or a failed deployment. Sometimes they show up quietly, during routine development, on a page that works perfectly but takes far longer to load than it should. That is exactly how this one surfaced: while testing a new feature, one page was noticeably slow. No errors, no failures, just a response time that did not match the size of the data being returned.

This post walks through how we diagnosed it, why SQL Server made the choice it did, and the index design that fixed it. The headline result: a single index took the query from roughly 10 seconds down to about 50 milliseconds, with no infrastructure changes and no application rewrite.

A slow query is usually a clue, not a verdict on your hardware. In this case SQL Server was running a full table scan because no index supported the query’s filter and sort. One well-designed composite index replaced the scan with an index seek and cut execution time from about 10 seconds to about 50 milliseconds, roughly a 200x improvement, without touching the server or the application code.

A database index is a separate, ordered data structure that lets the database engine locate rows without reading the entire table. It works like the index at the back of a book: instead of scanning every page to find a topic, the engine jumps straight to the right location. A well-matched index turns a full table scan into a targeted seek, which is the difference between reading thousands of rows and reading a handful.

Step 1: Isolate Where the Time Actually Goes

Before touching the database, we confirmed where the time was being spent. The application server was not under load, CPU and memory looked normal, and there was no sign of an infrastructure bottleneck. Breaking the request down by stage made the problem obvious: almost all of the response time was spent waiting on a single database query to return. Everything else, application logic, serialization, and network, was negligible by comparison.

This step matters because a slow page is easy to misread as an underpowered server. Here, more hardware would have done nothing. The query itself was the bottleneck, so that is where the work belonged.

Step 2: Read the Execution Plan

The query was not complex. It filtered on a couple of columns, applied a sort, and returned a modest result set. To understand why it took 10 seconds, we looked at the SQL Server execution plan, which shows the strategy the optimizer chose for retrieving the data. The same query can be executed in many different ways, and SQL Server picks the plan it estimates to be cheapest based on the available indexes and its statistics.

The plan showed a clustered index scan, effectively a full table scan. Instead of jumping straight to the rows it needed, SQL Server was reading a large portion of the table and discarding everything that did not match. A few other signals lined up with that diagnosis:

  • A thick arrow leaving the scan operator, meaning a large number of rows were read into the pipeline before any filtering.
  • A Sort operator to satisfy the ordering, which is expensive and memory-hungry on large inputs.
  • A green missing-index recommendation at the top of the plan, SQL Server’s own hint that it had no efficient path to the data.

The query was not wrong. It returned correct results every time. The database simply had no efficient route to the rows, so it fell back to the only reliable option it had: read everything, then filter. That distinction matters, because the fix is an indexing decision, not a code bug.

Step 3: The Query and Why the Scan Happened

The query looked similar to this:

At a glance it looks harmless: filter on two columns, sort the result. The problem was what the table offered to support that pattern, which was nothing useful. There was no index that led with TenantId and Status, so SQL Server could not seek directly to the matching rows. On a small table this is invisible. As the table grows, the cost of scanning every row on every execution grows with it, which is why this query performed fine early in the project and degraded over time as data accumulated.

Two more details made the scan worse than a simple filter. First, ORDER BY CreatedDate DESC forced a separate sort step, because the data was not retrieved in that order. Second, SELECT * pulled every column, which has consequences for the fix described below.

Step 4: Design the Right Index, Not Just an Index

It is tempting to read the missing-index hint, create exactly what it suggests, and move on. The hint is a starting point, not a finished design. Per Microsoft’s index design guidance, a good index for this query follows a clear order of columns:

Equality predicates first: TenantId and Status are matched with =, so they lead the key. This is what lets SQL Server perform an index seek straight to the relevant rows instead of scanning.

The sort column next: adding CreatedDate DESC to the key means rows come out of the index already in the requested order, which removes the expensive Sort operator entirely.

That gives us:

This is stronger than indexing only (TenantId, Status). That narrower index still helps SQL Server find the rows, but it leaves the ORDER BY unsupported, so the engine would still add a Sort. Including the sort column in key order avoids that work altogether.

Order your composite index keys as equality columns first, then the sort column, then range columns. Getting that sequence right is what lets a single index satisfy both the WHERE filter and the ORDER BY in one seek, with no separate sort step.

The SELECT * Problem and Covering Indexes

There is one more consideration. Because the query uses SELECT *, the index above still does not contain every column the query returns. SQL Server would seek the index to find matching rows, then perform a key lookup back to the clustered index for each row to fetch the remaining columns. On a large result set, thousands of key lookups can quietly undo much of the benefit. There are two clean ways to handle it:

  • Return only the columns you need instead of SELECT *, which is good practice regardless of indexing.
  • Add the required columns as included columns so the index can satisfy the whole query on its own, known as a covering index.

Included columns live at the leaf level of the index and are not part of the key, so they support the lookup without bloating the seek. The right choice depends on how many columns the page genuinely needs. The point is to make a deliberate decision rather than accept the default hint.

Step 5: Verify the Fix With Numbers, Not Vibes

After creating the index, we re-ran the query and re-examined the plan. The clustered index scan was replaced by an index seek, and the Sort operator was gone. To confirm it with hard numbers rather than wall-clock timing alone, we used the statistics output:

Logical reads dropped dramatically, which is the real measure of how much work a query does and is far more stable than a stopwatch on a busy server. The end-to-end results:

Metric Before (no index) After (composite index)
Query execution time ~10 seconds ~50 milliseconds
Data access method Clustered index scan Index seek
Sort operator Present Eliminated
Logical reads High A small fraction
Page response Noticeable delay Near-instant

And the change required none of the things teams usually reach for first: no infrastructure upgrades, no additional servers, no application rewrite, and no changes to business logic. A single, well-designed index delivered the entire improvement.

Why Indexing Is Worth Understanding, and Not Overdoing

Indexes are one of the most effective tools for database performance, but more indexes is not the goal. Every index carries a cost: it must be maintained on every insert, update, and delete, and it consumes storage. Over-indexing a write-heavy table can hurt as much as under-indexing a read-heavy one.

Do not treat SQL Server’s missing-index recommendations as instructions. They identify candidates based on a single query in isolation, with no awareness of your write patterns or existing indexes. Blindly creating every suggested index leads to duplicate and overlapping indexes that slow down writes and bloat storage. Use them as input to a deliberate design, not a to-do list.

Effective indexing comes from understanding how the application actually queries the data: which columns are filtered with equality versus ranges, what ordering queries request, which columns are returned often enough to justify covering them, and how write-heavy the table is. Query patterns also drift over time as features get added, reporting requirements change, and data volumes climb. Indexes that were adequate at launch can quietly become liabilities at scale, which is exactly why periodic performance reviews belong alongside new feature work, not after something breaks.

The Business Impact of a 10-Second Query

A 10-second delay can look trivial in isolation. Multiplied across hundreds or thousands of requests a day, it is not. Slow pages interrupt workflows, reduce productivity, and erode user confidence in a system that feels unresponsive. Reducing this query from 10 seconds to 50 milliseconds changed the experience of the whole page without any architectural change. That is the kind of leverage well-targeted database work offers: small, surgical, and disproportionately impactful.

Lessons Learned

  • Measure before you optimize. Confirm where the time actually goes before assuming it is the server, the code, or the network.
  • Read the execution plan. It tells you exactly how SQL Server runs your query: scan versus seek, sorts, lookups, and where the cost lives.
  • Design indexes, do not just create them. Equality columns first, then sort columns, and decide deliberately about covering the query.
  • Data growth exposes problems that were invisible early on. What scaled fine at launch may not scale at volume.
  • Review performance proactively. The best time to find this is during a routine review, not during an outage.

Sometimes the most impactful optimization is simply helping the database find data it already has more efficiently. Here, one composite index reduced execution time from seconds to milliseconds and delivered a meaningfully better experience for everyone using the application, with zero infrastructure spend. That is the kind of improvement every development team likes to find.

Proactive performance reviews like this are part of how we keep mid-market systems healthy before slowdowns turn into outages. If your team is seeing pages that feel slower than the data warrants, our managed IT and application support practice can help diagnose and resolve it. Get in touch with BALANCED+ to talk through a performance review.

Frequently Asked Questions

What is the difference between an index seek and an index scan?

An index seek uses the index structure to navigate directly to the rows that match a query, reading only what it needs. An index scan reads through the entire index or table and filters afterward. Seeks are typically far cheaper on large tables, and moving from a scan to a seek is one of the most common high-impact query optimizations.

Why was the query slow if it returned correct results?

Correctness and performance are separate concerns. The query returned the right rows every time, but with no supporting index SQL Server had to scan a large portion of the table and sort the results on every execution. Correct results with poor performance almost always point to a missing or mismatched index rather than a logic error.

Should I just create every index SQL Server recommends?

No. Missing-index recommendations are generated from individual queries in isolation and ignore your write workload and existing indexes. Acting on all of them creates redundant indexes that slow down inserts, updates, and deletes. Treat the recommendations as candidates and design a focused set of indexes based on your actual access patterns.

How often should we review database performance?

Schedule reviews on a recurring cadence and after any significant change in data volume, feature scope, or reporting requirements. Indexes that were adequate at launch can degrade as tables grow, so a periodic check of slow queries and execution plans catches problems before users feel them. For most mid-market systems, a quarterly review plus checks after major releases is a reasonable baseline.

Sources