[FEATURE] Implementing blackout window(s) to skip data entirely.

[pgfiore]

Which component(s) does this affect?

• Full Dashboard
• Lite
• SQL collection scripts
• Installer
• Documentation

Problem Statement

The collectors work on anything that happened in the system. Generally speaking, they always collect from the last run up to now. Overnight, we have just maintenance jobs and DW feeding; they do not add anything useful and, what’s worse, could blow the tempdb (#885).
Note: I'm not use to this environment; sorry for any misunderstanding. Furthermore, drop a msg to pgfiore@hotmail.it for any further consideration.

Proposed Solution

Adding a blackout window(s) when data is skipped in whole and will never ever be collected.

I see two main ways to implement it:

a) The simple approach involves adding a single daily time range (start time and end time) into the config.collection_schedule to define the blackout window.

b) The more thorough one uses a child table that defines multiple daily time ranges. And specific dates are not needed because the blackout window does repeat daily.

Use Case

-- CAN BE RUN IN PROD - IT DOES NOT CHANGE ANYTHING
-- this example is supposed to hack the collect.query_stats_collector in Stage 1: Collect query statistics into temp table
-- adding features to this clause: "WHERE qs.last_execution_time >= @cutoff_time"

drop table if exists #tblBlackouts
drop table if exists #tmpQueryStats
-- Blackout ranges
select start_time=convert(time(3), '23:00:00'), end_time=convert(time(3), '23:59:59.999')
into #tblBlackouts
insert into #tblBlackouts select '00:00:00', '01:59:59.999'
insert into #tblBlackouts select '15:00:00', '15:30:00.000'
-- select * from #tblBlackouts

-- debug data
-- select * from #tmpQueryStats
select top 5 *
into #tmpQueryStats
from sys.dm_exec_query_stats qs
where convert(time(3), qs.last_execution_time) between '23:00:00' and '23:59:59.999'
insert into #tmpQueryStats
select top 5 *
from sys.dm_exec_query_stats qs
where convert(time(3), qs.last_execution_time) between '22:00:00' and '22:59:59.999'

-- output queries that are not in the blackout ranges
select *
from #tmpQueryStats qs -- debug data
where not exists (select 1 from #tblBlackouts B where convert(time(3), qs.last_execution_time) between B.start_time and B.end_time)

select top 10 *
from sys.dm_exec_query_stats qs -- original data
cross apply (VALUES (convert(time(3), qs.last_execution_time))) as ca(lastexec_time) -- Used to assess the performance of the convert function
where not exists (select 1 from #tblBlackouts B where convert(time(3), ca.lastexec_time) between B.start_time and B.end_time)

Alternatives Considered

Additional Context

• I wonder if this blackout window should spread through all the collectors or only some of them…
• Can a collector run during blackout windows with zero rows (thank to this fix in the where clause)? Or is a fix in the collect.scheduled_master_collector is required to correctly set config.collection_schedule.next_run_time?

[erikdarlingdata]

Thanks for writing this up with a working prototype — the NOT EXISTS against a blackouts table is exactly the shape a real implementation would take, and the distinction between "skip the run" and "skip the data" is the right one to pull out.

Before going further on this, there's a piece of context: #888 (landing in tonight's nightly, dev branch) adds an Off preset to config.apply_collection_preset. Paired with a second Agent job that applies a non-Off preset at the start of your active window, you get overnight / quiet-hours scoping with two SQL Agent jobs and no collector changes:

-- Agent job: "Quiet hours - start" (schedule: daily 22:00)
EXECUTE config.apply_collection_preset @preset_name = N'Off';

-- Agent job: "Quiet hours - end" (schedule: daily 06:00)
EXECUTE config.apply_collection_preset @preset_name = N'Balanced';

For your original #885 case — query_stats_collector blowing tempdb overnight — that'll solve it, because the collector simply doesn't run during the window. The pathological plans never get pulled from the plan cache into the staging temp table.

The gap #892 fills that #888 doesn't: "keep collecting most things overnight, but don't store rows whose event timestamp falls in the blackout window." That's a different ask — cleaner trend data that excludes maintenance-window noise while still capturing everything else. Useful, but narrower, and with real design questions to resolve:

1. Applicability is uneven. Collectors with a meaningful event timestamp (default_trace_events.event_time, query_stats.last_execution_time, procedure_stats.last_execution_time, query_store_data.last_execution_time, the deadlock/blocked-process XE collectors) can honor a blackout filter cleanly. But snapshot collectors — wait_stats, memory_stats, perfmon_stats, cpu_utilization, file_io_stats — are "state at collection time." They don't have a "when did this happen" axis to filter on. They either collect or they don't. So the feature would only apply to ~6–8 collectors out of ~30. Worth calling that out explicitly in any design.

2. Option (a) — start/end columns on config.collection_schedule — is too restrictive. One daily range per collector can't express common real-world needs like "22:00–02:00 plus 14:00–14:30 (warehouse reload)." Option (b) — a child table keyed to collector (or NULL = all eligible collectors) with multiple ranges — is the right shape.

3. Midnight wrap. Your prototype already splits 23:00–23:59:59.999 + 00:00–01:59:59.999 rather than one 23:00–02:00 range, which avoids the wrap entirely — that's a sensible convention to make the rule in any implementation.

Your questions, answered:

Should the blackout window spread through all the collectors or only some of them?

Only event-timestamp-bearing ones (see #1 above). Attempting to apply it to snapshot collectors would be misleading.

Can a collector run during blackout windows with zero rows? Or is a fix in the collect.scheduled_master_collector required?

No dispatcher fix needed. The dispatcher advances next_run_time whether the collector inserted 0 rows or 10,000. Each collector's log entry to config.collection_log captures rows_collected, which will legitimately be smaller (or zero) when a window is active. Existing logic handles this cleanly.

My recommendation: try #888 first — it's merging tonight, and if your main pain is "stop the overnight blowup," it'll solve it without collector changes. If after living with that you still want per-row event-timestamp filtering for cleaner trend data, we've got a good design sketch here to come back to. That way #892 gets built against a concrete need rather than a speculative one, and we don't spend effort on a feature that turns out to be covered by a simpler primitive.

Either way, the design thinking here is useful — leaving this open even if we defer the implementation.

[pgfiore]

Thanks a lot, Erik. It was great, and I’ll definitely give the #888 a try.
But let me ask one more thing (I know I’ve asked this before—sorry). I’m still not clear on why stopping the Agent would prevent the issue.
If I understand correctly, collect.query_stats_collector reads from sys.dm_exec_query_stats with something like “WHERE qs.last_execution_time >= @cutoff_time”. And @cutoff_time is just last_collection_time, right?
So it would just read all the new stuff since last_collection_time… And I think #888 needs to move it forward (say to sysdate?) when it wakes the Agent job back up, does it sound?

[erikdarlingdata]

You're completely right, and I should have caught this before pitching #888. I verified against install/08_collect_query_stats.sql — the collector computes its cutoff from MAX(qs.collection_time) FROM collect.query_stats, then pulls everything in sys.dm_exec_query_stats where last_execution_time >= @cutoff_time. So after an overnight Off, the 06:00 catch-up run will happily sweep up whatever pathological plans are still in the cache — the blowup just moves from 02:00 to 06:00.

What I'd like to do about it:

A narrow extension on top of #888 — a "collection resumed" marker — rather than building the full #892 blackout system.

Shape:

1. Add a collection_resumed_time datetime2(7) NULL column to config.collection_schedule.
2. The Off preset clears it (sets to NULL).
3. Any non-Off preset, when switching from an Off state, sets it to SYSDATETIME().
4. Affected collectors (query_stats, procedure_stats, query_store, the XE-backed ones) read it and use @cutoff_time = GREATEST(@last_collection_time, ISNULL(@collection_resumed_time, '19000101')). First post-resume run only looks at queries executed after the transition; subsequent runs behave normally.

Why not go full #892:

Two reasons. First, writing our own scheduler logic is a rabbit hole — time zones, DST, midnight wrap, "what if the Agent itself was down during a blackout," per-collector vs global scope, expressive syntax for multiple windows, catch-up semantics, etc. SQL Agent already owns all of that. Composing blackout windows by flipping presets with Agent jobs keeps the scheduling logic where scheduling belongs, and our code only needs to answer one question ("is the next run a resume-from-Off run or a normal run?"). That's a much smaller surface to get right.

Second, the resume marker covers your actual #885 pain deterministically. The only thing it doesn't cover that #892 would: if you want to keep most collectors running overnight but exclude a narrow maintenance window from their data. That's a real use case, just not yours today.

The tradeoff:

You lose signal for queries whose last_execution_time falls inside the blackout window — exactly the rows you want to drop, but also exactly the rows that would show up as "slow query at 03:00" on a day when you actually want to investigate that. If you're using this for "don't store maintenance noise," that's a feature. If you're using it for "don't tempdb-blow-up my collector," same outcome either way.

Does this shape work for you, or is the "I lose data from the blackout window" piece a dealbreaker that pushes us toward the full #892?

[erikdarlingdata]

Shipped in PR #904 (merged to dev). Implemented as a long-gap detection heuristic in query_stats / procedure_stats / query_store rather than the explicit resume marker — same behavior, no schema change, also covers Agent stoppages and server reboots.