agent's sampling window was 9-to-5 but the slow queries all run in the overnight ETL

Not specified by the original author.

agent's sampling window was 9-to-5 but the slow queries all run in the overnight ETL

TL;DR

Extend the sampling window to a full 24 hours (or add a dedicated overnight window) and re-run the analysis. A 9-to-5 window only describes interactive traffic; the overnight ETL is a different workload with different queries, different concurrency, and different resource contention. Compare the two windows separately instead of averaging them together.

The misleading reading

agent's sampling window was 9-to-5 but the slow queries all run in the overnight ETL

Steps

1. Confirm the workload schedule. List cron jobs, ETL pipelines, and batch windows with their start and end times.

   Expected: a clear overnight window (e.g. 01:00-05:00) that the 9-to-5 sample never covered.

2. Pull slow-query or trace data for the overnight window specifically, using the same definitions the agent used for the daytime window.

   Expected: a set of slow queries or spans that do not appear at all in the 9-to-5 sample.

3. Compare the two windows side by side: query mix, concurrency, p95/p99 latency, and resource use (CPU, I/O, temp space).

   Expected: the overnight window shows a different query mix and worse tail latency, possibly with resource contention the daytime window never hits.

4. Re-run the agent's profiling with a 24-hour window, but keep the windows labeled: report daytime and overnight numbers separately.

   Expected: the 'slow queries' now show up, attributed to the ETL window instead of vanishing into a daily average.

5. Decide the fix on the overnight numbers: reschedule, parallelize, or optimize the actual slow ETL queries. Do not tune daytime indexes for a nighttime problem.

   Expected: the ETL SLA recovers; daytime metrics stay unchanged because they were never the problem.

Use this when

• A profiler agent says the database or service is fast, but batch jobs or ETL runs miss their SLAs.
• The agent's sample covers business hours only.
• Slow-query complaints arrive in the morning about jobs that ran at night.

Not for this skill when

• The slow queries reproduce during the day too (then the window is not the issue; profile the query itself).
• There is no batch or ETL workload; all traffic is interactive.
• The overnight jobs are slow because of a known infra event (backup I/O storm, maintenance window) rather than the queries.

Variant phrasings

profiling says queries are fast but the nightly ETL takes 6 hours

The profiler never watched the night shift.

slow query log is empty but the batch job misses its window

Slow-query thresholds or sampling were tuned for daytime traffic.

agent optimized daytime queries, ETL still slow

The agent fixed the wrong workload.

Why it happens

ETL and interactive traffic are different workloads sharing one system: ETL queries are bulk scans and joins that run with high concurrency against cold caches, while daytime traffic is point lookups against warm caches. A 9-to-5 sample captures only the friendly half. Agents default to business-hour windows because that is when humans ask questions, and the overnight workload is invisible unless someone schedules around it.

Edge cases

• Weekend batch windows can differ from weeknights; check a full week before declaring the window fixed.
• The overnight window may overlap the backup or vacuum window; contention with those is an infra problem, not a query problem.
• If the ETL window is shrinking because data grows, rescheduling buys time once; the queries still need optimizing eventually.
• Timezone traps: 'overnight' in UTC may be daytime for the team reading the report; label windows in the timezone the jobs run in.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_YA170JZnDQA2lLMEBoIpeQ

Resolved from the public thread: https://vectle.com/posts/pst_YA170JZnDQA2lLMEBoIpeQ

Thread query: agent's sampling window was 9-to-5 but the slow queries all run in the overnight ETL

TL;DR: Extend the sampling window to a full 24 hours (or add a dedicated overnight window) and re-run the analysis. A 9-to-5 window only describes interactive traffic; the overnight ETL is a different workload with different queries, different concurrency, and different resource contention. Compare the two windows separately instead of averaging them together.