[BUG] query_store_collector: cutoff_time bug causes full re-collection on every run, leading to tempdb exhaustion and database explosion

[ydeo-fcourtaud]

Component

SQL collection scripts

Performance Monitor Version

PerformanceMonitor v2.0.0

SQL Server Version

SQL Server 2019 Standard Edition (15.0.2130.3)

Windows Version

Windows Server 2019

Describe the Bug

Three related bugs in collect.query_store_collector that combine to cause
catastrophic resource consumption on environments with large Query Store databases.

Bug 1 — Wrong cutoff_time calculation (Critical)
The cutoff time is calculated as:

SELECT @last_collection_time = MAX(qsd.utc_last_execution_time)
FROM collect.query_store_data AS qsd;

This uses the last observed query execution time from collected data,
not the last time the collector actually ran. Since utc_last_execution_time
reflects when queries were executed in monitored databases rather than
when they were collected, the MAX value does not advance with each
collection cycle. The cutoff stays anchored in the past, causing full
re-collection of all historical data on every run.

Bug 2 — First run collects 3 days of history regardless of Query Store size (High)
The hardcoded 3-day lookback on first run is dangerous on environments
with large Query Store databases. With 5.6 GB of Query Store data spread
across 9 databases, this means decompressing thousands of XML execution
plans in a single run.

Bug 3 — Default frequency too aggressive for heavy collectors (Medium)
query_store_collector defaults to every 2 minutes with a
max_duration_minutes of 10, while the Hung Job Monitor threshold
defaults to 5 minutes. On large environments the collector never finishes
before being killed.

Steps to Reproduce

1. Install PerformanceMonitor v2.0.0 on a SQL Server instance with
   multiple databases having Query Store enabled and significant history
   (in our case: 9 databases, ~5.6 GB total Query Store data,
   5 databases between 790 MB and 870 MB each)
2. Let the Collection job run at its default schedule (every 2 minutes)
3. Observe the job running indefinitely without completing
4. Check sp_WhoIsActive — the query_store_collector session will show
   hundreds of millions of reads and massive tempdb consumption
5. After 5 minutes the Hung Job Monitor kills the job
6. The job restarts and the cycle repeats
7. Overnight: PerformanceMonitor database grows by 150+ GB,
   tempdb exhausted, server out of disk space

Expected Behavior

• First run should collect a reasonable window of data
  (e.g. frequency_minutes interval, not 3 days)
• Subsequent runs should collect only data since the last
  successful collection, based on when the collector ran
  rather than when queries were last executed
• Each collection cycle should complete well within the
  configured max_duration_minutes

Actual Behavior

• Collector ran for 47+ minutes without completing
• 330 million reads, 28 million writes in a single run
• 150+ GB of tempdb allocated (spilling to disk)
• PAGEIOLATCH_SH / PAGEIOLATCH_EX waits on tempdb throughout
• PerformanceMonitor database grew by 150+ GB overnight
• Hung Job Monitor killed the job after 5 minutes on every run
• No data was ever successfully collected (all rolled back)

Error Messages / Log Output

sp_WhoIsActive output at peak:

    duration:          00:47:21
    wait_type:         PAGEIOLATCH_SH / PAGEIOLATCH_EX (tempdb)
    CPU:               1,112,600 ms
    reads:             330,924,462
    writes:             28,505,148
    tempdb_allocations: 23,736,504 pages (~185 GB)
    tempdb_current:      5,894,024 pages (~46 GB)
    open_tran_count:     2

config.collection_log:

    collector_name:      hung_job_monitor
    collection_status:   JOB_HUNG
    error_message:       Job "PerformanceMonitor - Collection" hung for 
                         7 minutes (threshold: 5 minutes). Stopping job.

Screenshots

No response

Additional Context

Environment:

• SQL Server 2019 Standard Edition (15.0.2130.3)
• Windows Server 2019
• PerformanceMonitor v2.0.0
• 9 databases with Query Store enabled
• Query Store sizes: 12 MB to 867 MB per database,
  5 databases between 790 MB and 870 MB
• Total Query Store data: ~5.6 GB

Proposed fixes:

Bug 1 — Replace MAX(utc_last_execution_time) with last successful
collection time from config.collection_log:

-- BEFORE:
SELECT @last_collection_time = MAX(qsd.utc_last_execution_time)
FROM collect.query_store_data AS qsd;

-- AFTER:
SELECT TOP (1)
    @last_collection_time = TODATETIMEOFFSET(cl.collection_time, 0)
FROM config.collection_log AS cl
WHERE cl.collector_name = N'query_store_collector'
AND   cl.collection_status = N'SUCCESS'
ORDER BY cl.collection_time DESC;

Bug 2 — Replace 3-day hardcoded lookback with frequency_minutes interval:

-- BEFORE:
SET @cutoff_time = TODATETIMEOFFSET(DATEADD(DAY, -3, SYSUTCDATETIME()), 0);

-- AFTER:
SET @cutoff_time = TODATETIMEOFFSET(
    DATEADD(MINUTE, -ISNULL(@collection_interval_minutes, 30), SYSUTCDATETIME()),
    0
);

Bug 3 — Increase default frequency for heavy collectors in installation scripts:

UPDATE config.collection_schedule
SET frequency_minutes = 15, max_duration_minutes = 12
WHERE collector_name IN
(
    N'query_store_collector',
    N'query_stats_collector',
    N'procedure_stats_collector'
);

-- And increase @normal_max_duration_minutes from 5 to 15 
-- in 45_create_agent_jobs.sql for the Hung Job Monitor.

Workaround applied while waiting for fix:
Inserted a seed row in collect.query_store_data to anchor
MAX(utc_last_execution_time) to the current time, preventing
the 3-day first-run lookback from triggering on reinstallation.

[erikdarlingdata]

Thanks for the detailed report and the real-world numbers — a 5.6 GB Query Store environment across 9 databases is exactly the kind of edge case that stress-tests this collector. Let me go through each bug:

Bug 1 — cutoff_time calculation

This one is actually working as designed. MAX(utc_last_execution_time) does advance with each collection cycle because:

1. Each run inserts rows with their utc_last_execution_time from Query Store
2. As new queries execute in monitored databases, their last_execution_time values are newer than what was previously collected
3. Next collection picks up MAX() of those newly inserted rows, which is a more recent timestamp

The key insight: this isn't using the collector's run time as the cutoff — it's using the most recent query execution time we've seen. That's actually more precise, because it avoids gaps if the collector was delayed or skipped a cycle. The MAX() value marches forward as long as queries are executing in the monitored databases.

Your proposed fix (using collection_log.collection_time) would actually create a subtle bug: if the collector ran at 10:00 but the most recent query execution was at 9:58, the next run using collection_time = 10:00 as cutoff would miss queries that executed between 9:58 and 10:00.

Bug 2 — 3-day first-run lookback (Real issue)

This is the actual problem. On your environment with 5.6 GB of Query Store data, the 3-day lookback pulls an enormous dataset including XML plan decompression, which is the expensive part. The sequence is:

1. First run → 3-day lookback → times out → rolls back (no data persisted)
2. First-run detection checks NOT EXISTS (SELECT 1/0 FROM collect.query_store_data) → still empty because the rollback wiped everything
3. NOT EXISTS (SELECT 1/0 FROM config.collection_log WHERE collector_name = N'query_store_collector') → also empty because no successful run was logged
4. → Goto 1. Infinite loop.

The timeout→rollback→retry loop means the first-run detection fires every single time, and the 3-day window never gets narrower. This explains the 150+ GB growth overnight.

Bug 3 — Frequency

The 2-minute default with 10-minute max_duration_minutes is fine for typical environments. Your environment hit this because of Bug 2 — once the first-run loop is fixed, normal incremental collection should complete well within 2 minutes.

Fix approach

We're looking at an adaptive first-run lookback that probes the Query Store row count at progressively narrower windows (3 days → 1 day → 8 hours → 1 hour) and picks the first window under a safe threshold. This prevents the catastrophic first run without losing all historical backfill for smaller environments.

In the meantime, your workaround of seeding a row to anchor MAX(utc_last_execution_time) is a solid approach — it bypasses the first-run detection entirely.

We'll keep this open while we work on the fix.

[ydeo-fcourtaud]

Thanks for your feedback and also for the amazing work :)

[erikdarlingdata]

Fixed in PR #340 (merged to dev).

All 8 collectors with first-run backfill have been reduced from 3-7 days (or unlimited) to a 1-hour lookback window. The original aggressive lookback values were a development convenience to make sure data was populating during initial testing — they were never intended for production environments with large data volumes like yours.

With 1 hour of lookback, the first run will complete quickly and subsequent incremental collections will keep things current from there. The timeout→rollback→retry loop you experienced is eliminated because 1 hour of Query Store data is bounded and won't cause the catastrophic XML plan decompression that a 3-day window triggers on multi-GB environments.

This fix will be included in the next release. In the meantime, your workaround of seeding a row to anchor MAX(utc_last_execution_time) works great.

Thanks for using Performance Monitor and for the detailed report — the real-world numbers from your 9-database environment were exactly what we needed to understand the impact.

[erikdarlingdata]

Fix is in on dev. If you'd like to test before the next stable release, it'll be in tonight's nightly build: https://github.com/erikdarlingdata/PerformanceMonitor/releases/tag/nightly