The report that worked on Today and timed out on Month

The symptom. The same KPI report returned instantly for Today and timed out for week-to-date and month-to-date. Reported, reasonably, as “the dashboard is slow.”

What it actually was. The week and month views filter invoice-history on a pick-date column. That column was not indexed. The indexes that existed on the table covered the order number, a history id and line, the lot, and a rowguid — every access path except the one the date filter needed. So the date range forced a full scan of the entire invoice-history table. Today was fast because it read a different, far smaller table; the period views were slow because they touched history, and the shape of the query meant touching history meant reading all of it.

This is the most common performance root cause in ERP reporting and the one most often misdiagnosed, because the symptom scales with the date range and therefore looks like data volume. It is not a volume problem. It is an access-path problem that volume makes visible.

Why the fix needed more care than the diagnosis. The tables shipped with the ERP; they were not locally created. Adding an index to a vendor schema is a supportability decision before it is a technical one, so the work went to the platform owner before anything was applied. Beyond that:

• Index creation takes a schema-modification lock on SQL Server Standard Edition. It blocks live users for the duration of the build, so it belongs in a maintenance window, not in the afternoon it was diagnosed.
• An idle query window holding an open transaction will block the build and create a lock chain that looks, to everyone else, like the database has frozen. Close the tabs first. This sounds trivial until it happens.
• Size and current index state get captured before anything changes, so the change can be evaluated and reversed.

The transferable part. Written up in full as why an ERP report is fast on today and times out on month-to-date. In short: when a report is fast on a short window and dies on a longer one, resist the instinct to optimise the query. Look at what the date filter is actually filtering, and whether anything indexes it. The fix is frequently one index. The risk is almost never in the index — it is in applying it to a vendor schema, on the wrong edition, at the wrong time of day.

Worth measuring if you run this

• Report runtime before and after, at each period setting. Capture it before you apply the index; you cannot go back for it.
• Logical reads for the query, which is the honest measure and does not move with server load.
• Index build duration, so the next maintenance window can be planned rather than guessed.