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
| Metric | Before | After |
|---|---|---|
| Elapsed time | 396 ms | 52 ms |
| CPU time | 789 ms | 52 ms |
| Rows read from the scanned table | 6,943,378 | 13 |
| Memory grant | 56,032 KB | 1,472 KB |
These figures compare measured single executions. The revised query was also checked across eight tenant sizes.
