How a 5–6 hour reporting workload became a predictable 45-minute run.

01 • Situation

The nightly job had become a morning problem.

A SQL Server reporting workload regularly needed 5–6 hours to finish. As data volume and query complexity grew, jobs began spilling into business hours. Reports arrived late, downstream teams waited, and larger compute was being used to compensate for a workload nobody had fully measured.

02 • Diagnosis

Start with evidence, not guesses.

The investigation used runtime history, wait patterns, execution plans, table access patterns, job sequencing, and resource utilization. That separated the visible symptoms from the small number of workload paths creating most of the delay.

1. Baseline: Capture runtime, CPU, reads, waits, and plan behavior over several representative nights.
2. Hotspots: Identify the queries and tables consuming the largest share of the reporting window.
3. Dependencies: Map job overlap, downstream timing, and safe sequencing constraints.
4. Cost: Compare current compute with the resources the tuned workload would actually require.

03 • Changes

Fix the workload before replacing the platform.

The result came from several coordinated changes. There was no magic index and no single query rewrite that accounted for the entire improvement.

04 • Simplified query pattern

Make the predicate usable, then return only what is needed.

The following SQL is a simplified illustration of patterns addressed during the engagement. It is not the client’s production code.

SELECT *
FROM Orders
WHERE CONVERT(date, OrderDate) = '2025-01-31'
  AND CustomerId IN (
      SELECT CustomerId
      FROM Customers
      WHERE Country = 'US'
  )
  AND Status <> 'Cancelled';

SELECT  o.OrderId,
        o.OrderDate,
        o.CustomerId,
        o.Status
FROM dbo.Orders AS o
JOIN dbo.Customers AS c
  ON c.CustomerId = o.CustomerId
WHERE o.OrderDate >= '2025-01-31'
  AND o.OrderDate <  '2025-02-01'
  AND c.Country = 'US'
  AND o.Status <> 'Cancelled'
  AND o.Status IS NOT NULL;

• The date range can use an appropriate index on OrderDate.
• The select list avoids moving and processing unused columns.
• The direct join makes the relationship and access pattern explicit.
• The status predicate preserves the intended handling of null values.

05 • Validated outcome

The real improvement was predictability.

Reports were ready before the workday, support staff had useful monitoring, and the environment no longer needed excess compute as insurance against an unpredictable reporting window.