← DB Alchemy home

SQL Server performance case study

When Fast Queries Collide

A query returned ten rows in 396 ms. When several application requests ran it at once, it slowed sharply. The execution plan revealed how much work each request was repeating.

Measured case study

Before and after tuning

87%lower elapsed time

Before tuning396 ms
After tuning52 ms

Measured elapsed time for a single execution.

What the plan showed

Each execution read 6.94 million rows from one table and requested roughly 56 MB of workspace memory. Multiple overlapping calls repeated that scan and competed for memory, CPU and workers.

What changed

An index made it possible to seek by plan ID and due date. A filtered index gave the optimiser better statistics for the active subset and helped it choose a better join order. The final plan used those statistics during compilation. OPTION (RECOMPILE) let the optimiser account for differences in data distribution across tenants.

Measured results

MetricBeforeAfter
Elapsed time396 ms52 ms
CPU time789 ms52 ms
Rows read from the scanned table6,943,37813
Memory grant56,032 KB1,472 KB

These figures compare measured single executions. The revised query was also checked across eight tenant sizes.

Contact Neil

Seeing a similar slowdown?

Tell me what users are seeing, when it started, and what you’ve already tried. We can discuss where to begin.