[BUG] Scheduled Master Collector is blowing up tempdb

[pgfiore]

Component

Full Dashboard

Performance Monitor Version

1.7.0.0

SQL Server Version

SQL Server 2022 Dev. RTM-CU23-GDR

Windows Version

Azure Windows Server 2022 Datacenter

Describe the Bug

Scheduled Master Collector is blowing up tempdb every now and then (roughly once a week).

Steps to Reproduce

Not reproducible

Expected Behavior

... well, say not to stop the prod server? ;-)

Actual Behavior

I think I've found the job in running status though

Error Messages / Log Output

In config.collection_log I’m seeing errors like: “Error in query stats collector: Could not allocate space for object 'dbo.Large Object Storage System object:  422796401377280' in database 'tempdb' because the 'PRIMARY' filegroup is full”.

Screenshots

Additional Context

Since this is a production DB, we have to restart it immediately when it happens, so we can’t really inspect what’s going on at that exact moment. However, tempdb has four files to grow up to 60GB each, and they're usually pretty empty.

Any ideas on how to dig into this and figure out the root cause? Thank you

[erikdarlingdata]

Thanks for the report — a few things to unpack here.

Version first: 1.7.0.0 isn't a version that was ever released — the v1.x line ended at v1.3.0 before jumping to v2.0.0. Current release is v2.8.0. I'd strongly recommend upgrading before anything else; several directly relevant fixes have landed since v1.x:

• [BUG] query_stats_collector: query_plan_text collection causes unsustainable database growth #337 — optional query text / plan collection flags
• [BUG] Arithmetic Overflow Error #547 — arithmetic overflow in query_stats collector
• [BUG] On v2.3.0 SQL Dumps on Passive Mirrored SQL DB #632 — SQL dumps on mirroring passive / RESTORING databases
• [QUESTION] - Application database size and uninstaller? #419 — LOB compression + dedup for query_stats tables

Root cause: The error (Large Object Storage System object in tempdb) is almost certainly the #query_stats_staging temp table inside collect.query_stats_collector. It stages query_text and query_plan_text as nvarchar(max) — those land in tempdb LOB storage. The insert has no row cap and pulls the full XML plan for every query in sys.dm_exec_query_stats since the last run.

On a server with a busy plan cache, one run after a plan cache flush (memory pressure, stats updates, scheduled DBCC FREEPROCCACHE, a restart, etc.) can catch thousands of queries with huge XML plans in a single collection, and a handful of pathological 50–100 MB plans is enough to blow past even 240 GB of tempdb LOB space. The "roughly once a week" cadence lines up with plan cache churn on a busy production instance.

Mitigations (after upgrading):

1. Disable plan collection in this collector — Query Store / Plan Viewer still work, and the plan XML here is the storage hog:

   UPDATE config.collection_schedule
   SET    collect_plan = 0
   WHERE  collector_name = N'query_stats_collector';

2. Back off the frequency and raise the duration ceiling on the heavy collectors:

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

To help diagnose: next time it happens (or now, since the rows are still there), can you share:

1. config.collection_log rows for the hour leading up to the tempdb full error — specifically rows_collected, duration_ms, collection_status, and error_message for query_stats_collector. Something like:

   SELECT TOP (200) *
   FROM   config.collection_log
   WHERE  collector_name = N'query_stats_collector'
   ORDER BY collection_time DESC;

2. Current row count and LOB footprint of collect.query_stats:

   SELECT
       rows_mm        = COUNT_BIG(*) / 1000000.0,
       query_text_gb  = SUM(DATALENGTH(query_text))      / 1024.0 / 1024 / 1024,
       plan_text_gb   = SUM(DATALENGTH(query_plan_text)) / 1024.0 / 1024 / 1024,
       avg_kb_per_row = SUM(DATALENGTH(query_text) + DATALENGTH(query_plan_text))
                        / NULLIF(COUNT_BIG(*), 0) / 1024.0
   FROM collect.query_stats;

3. Whether anything external truncates or clears collect.query_stats / config.collection_log — if the log is ever emptied, the first-run path re-triggers and pulls the entire plan cache back to 19000101, which absolutely will blow tempdb on a big server.

That'll tell us whether this is steady-state plan-cache churn (mitigated by collect_plan = 0) or a first-run re-trigger (different fix).

[pgfiore]

Thank you for the prompt reply. And apologies for my typo:

Gonna digest your notes now...

[pgfiore]

I was thinking there might be some overnight maintenance jobs (e.g., data warehouse rebuilds) that not only bother the collectors but also mess up the stats.

Could I configure Performance Monitor to skip them, or maybe just exclude a specific time window?

Current row count and LOB footprint of collect.query_stats

rows_mm	query_text_gb	plan_text_gb	avg_kb_per_row
4.497562000	1.5743546029548632	20.9053649939594238	5.236328

config.collection_log rows when then the error occured:

collection_time	collector_name	collection_status	rows_collected	duration_ms	error_message
2026-04-23 07:27:06	query_stats_collector	ERROR	0	359800	Error in query stats coll. […]
2026-04-23 07:27:06	query_stats_collector	ERROR	0	359791	Could not allocate space […]
2026-04-23 07:18:23	query_stats_collector	SUCCESS	818	18491	NULL
2026-04-23 07:15:46	query_stats_collector	SUCCESS	1403	35996	NULL
2026-04-23 07:12:29	query_stats_collector	SUCCESS	718	20691	NULL
2026-04-23 07:10:21	query_stats_collector	SUCCESS	984	20994	NULL
2026-04-23 07:07:37	query_stats_collector	SUCCESS	1402	36668	NULL
2026-04-23 07:04:21	query_stats_collector	SUCCESS	186	18191	NULL
2026-04-23 07:01:49	query_stats_collector	SUCCESS	911	46202	NULL
2026-04-23 06:58:22	query_stats_collector	SUCCESS	750	18456	NULL
2026-04-23 06:55:34	query_stats_collector	SUCCESS	118	29500	NULL
2026-04-23 06:53:17	query_stats_collector	SUCCESS	68	17176	NULL
2026-04-23 06:50:33	query_stats_collector	SUCCESS	736	32605	NULL
2026-04-23 06:47:21	query_stats_collector	SUCCESS	69	17161	NULL
2026-04-23 06:44:18	query_stats_collector	SUCCESS	89	17051	NULL
2026-04-23 06:41:19	query_stats_collector	SUCCESS	99	17629	NULL
2026-04-23 06:38:18	query_stats_collector	SUCCESS	298	17787	NULL
2026-04-23 06:35:32	query_stats_collector	SUCCESS	174	17328	NULL
2026-04-23 06:33:17	query_stats_collector	SUCCESS	259	17313	NULL
2026-04-23 06:30:31	query_stats_collector	SUCCESS	1151	28939	NULL
2026-04-23 06:28:17	query_stats_collector	SUCCESS	90	17014	NULL
2026-04-23 06:25:22	query_stats_collector	SUCCESS	82	16660	NULL
2026-04-23 06:22:21	query_stats_collector	SUCCESS	97	16941	NULL
2026-04-23 06:20:17	query_stats_collector	SUCCESS	185	16815	NULL
2026-04-23 06:18:25	query_stats_collector	SUCCESS	590	25493	NULL
2026-04-23 06:15:17	query_stats_collector	SUCCESS	81	16706	NULL
2026-04-23 06:12:17	query_stats_collector	SUCCESS	181	16799	NULL
2026-04-23 06:09:21	query_stats_collector	SUCCESS	130	17439	NULL
2026-04-23 06:06:20	query_stats_collector	SUCCESS	537	18620	NULL

[erikdarlingdata]

Perfect, no worries on the typo — v2.7.0.0 is current enough that the fixes I listed are already in your build.

Your data confirms it's the plan XML:

• 20.9 GB plan text vs 1.57 GB query text — plan text is 93% of your storage, identical to the pattern in [BUG] query_stats_collector: query_plan_text collection causes unsustainable database growth #337.
• Average 5.2 KB/row looks fine, but the tail is what kills tempdb: one ad-hoc query with a 100+ MB XML plan, caught during a single staging INSERT, is plenty to blow past any reasonable tempdb allocation.

The collection log tells a very specific story:

Normal runs: 70–1,400 rows, 16–46 seconds each. Then at 07:27:06 you have two errors logged, both with rows_collected = 0 and duration_ms ≈ 360,000 (~6 minutes). Zero rows committed after 6 minutes of work means the failure happened during the staging INSERT — i.e., while pulling plan XML from sys.dm_exec_text_query_plan, before a single row reached the permanent table. That's the classic signature of a pathological plan (or a handful of them) filling tempdb LOB space mid-insert.

Also worth noting: 06:06–07:18 all succeeded with modest row counts. Whatever exploded the 07:27 run happened between 07:18 and 07:27 — so this looks more like a specific query/plan that hit the cache than overnight maintenance leaking in. Maintenance jobs at ~02:00–06:00 would have affected the earlier runs first, and they didn't.

On your time-window question:

There's no built-in blackout/quiet-hours feature in config.collection_schedule — it's a straight enabled bit plus frequency_minutes. If you genuinely want to skip a window, the cleanest options are:

1. Two SQL Agent jobs that flip enabled on a schedule:

   -- "Disable query_stats at 01:55"
   UPDATE config.collection_schedule
   SET    enabled = 0
   WHERE  collector_name = N'query_stats_collector';
   
   -- "Enable query_stats at 06:05" (separate job, separate schedule)
   UPDATE config.collection_schedule
   SET    enabled = 1
   WHERE  collector_name = N'query_stats_collector';

2. Or disable the parent Collection Agent job on a schedule window — same effect, blunter instrument (kills all collectors, not just the heavy ones).

But honestly, I'd skip the time window entirely and just do this:

UPDATE config.collection_schedule
SET    collect_plan = 0
WHERE  collector_name = N'query_stats_collector';

That setting is exactly what #337 added for this scenario. It solves the problem at its source — no more plan XML in the staging table, no more tempdb LOB pressure, no more weekly surprise regardless of what maintenance jobs or cache churn do. The Plan Viewer will still work for queries captured by query_store_collector, which is a better plan source anyway since it stores plans deduplicated by query_id / plan_id rather than per-statement-offset.

You can also reclaim the existing 20.9 GB by nulling out query_plan_text on historical rows if you want — the dashboard tolerates NULL plans gracefully.

[erikdarlingdata]

One more thing — here's a quick validation query to confirm your schema actually has the compressed LOB columns (from #419). If any of these come back as nvarchar instead of varbinary, the upgrade didn't fully apply and the COMPRESS() calls in the collectors would be wrapping what's already text, which is its own mess:

USE PerformanceMonitor;

SELECT
    schema_name   = s.name,
    table_name    = t.name,
    column_name   = c.name,
    data_type     = ty.name,
    is_compressed = CASE WHEN ty.name = N'varbinary' THEN 1 ELSE 0 END,
    expected_type = N'varbinary'
FROM sys.columns  AS c
JOIN sys.tables   AS t  ON t.object_id = c.object_id
JOIN sys.schemas  AS s  ON s.schema_id = t.schema_id
JOIN sys.types    AS ty ON ty.user_type_id = c.user_type_id
WHERE s.name = N'collect'
AND   (
          (t.name = N'query_stats'       AND c.name IN (N'query_text', N'query_plan_text'))
       OR (t.name = N'procedure_stats'   AND c.name = N'query_plan_text')
       OR (t.name = N'query_store_data'  AND c.name IN (N'query_plan_text', N'compilation_metrics'))
      )
ORDER BY t.name, c.name;

Expected: 5 rows, all data_type = varbinary, all is_compressed = 1.

If any row shows nvarchar, the table predates #419 and needs to be rebuilt. Easiest path in that case is to truncate the affected table (you'll lose history for that collector) and let the next collection recreate rows in the compressed format, since the INSERT in the collectors is already calling COMPRESS() on the way in.

You can also sanity-check that compression is actually happening on new rows:

SELECT TOP (20)
    collection_time,
    query_text_compressed_kb      = DATALENGTH(query_text)      / 1024.0,
    query_text_uncompressed_kb    = DATALENGTH(DECOMPRESS(query_text))      / 1024.0,
    plan_text_compressed_kb       = DATALENGTH(query_plan_text) / 1024.0,
    plan_text_uncompressed_kb     = DATALENGTH(DECOMPRESS(query_plan_text)) / 1024.0,
    compression_ratio             =
        CAST(DATALENGTH(DECOMPRESS(query_plan_text)) AS decimal(18,2))
        / NULLIF(DATALENGTH(query_plan_text), 0)
FROM collect.query_stats
WHERE query_plan_text IS NOT NULL
ORDER BY collection_time DESC;

If compression_ratio is reliably >2 you're getting real compression. If it's ~1.0, COMPRESS() is essentially a no-op on that data (usually means the source was already binary-ish, which shouldn't be the case for XML plan text) and it's worth investigating further.

[pgfiore]

Damn, super impressed—appreciate it, thanks! Lemme ask one more thing: I don’t want anything going on overnight—server, memory, locks, stats, CPU, queries, nada. What’s the easiest way to just shut it all down?

[erikdarlingdata]

Easiest path — modify the existing Agent job's schedule to only be active during a daily window rather than 24/7. The collection job ships with a schedule named Every 1 Minute and by default has no active-time window (i.e., runs all day). Adding one takes one call:

USE msdb;

EXECUTE msdb.dbo.sp_update_schedule
    @name              = N'Every 1 Minute',
    @active_start_time = 060000,   /*06:00 AM — start collecting*/
    @active_end_time   = 220000;   /*10:00 PM — stop collecting*/

Between 22:00 and 06:00 the Agent scheduler simply won't fire the job — no collectors run, no DMV queries, no tempdb LOB work, nothing touching your server. At 06:00 it resumes on its normal 1-minute cadence.

A couple of small things to keep in mind:

1. The scheduled master collector self-heals on each wake-up. The config.collection_schedule table tracks next_run_time per collector, but the master proc will run anything whose next_run_time <= SYSDATETIME() on the first execution after 06:00. So immediately after the window reopens, you'll get one "catch-up" pass where essentially every collector runs back-to-back. That's fine — it's what you want — but it'll be a heavier-than-normal first minute. If you want to smooth that, bump your heavy collectors' frequency_minutes first.

2. The Hung Job Monitor job (PerformanceMonitor - Hung Job Monitor) will still run in the background and is cheap — no reason to touch it.

3. Data Retention job runs daily at 02:00 by default (@active_start_time = 20000 in 45_create_agent_jobs.sql:176). If you really want nothing going on overnight, either move it to a time in your active window:

   EXECUTE msdb.dbo.sp_update_schedule
       @name              = N'Daily at 2 AM',  /*check actual schedule name first*/
       @active_start_time = 070000;            /*move to 07:00 AM*/

   Or leave it alone — retention is a targeted DELETE loop, not a DMV sweep, so it won't touch the plan cache or tempdb the way the collectors do.

If you ever want to confirm the window took effect:

SELECT
    job_name           = j.name,
    schedule_name      = s.name,
    enabled            = s.enabled,
    active_start_time  = s.active_start_time,
    active_end_time    = s.active_end_time
FROM msdb.dbo.sysjobs            AS j
JOIN msdb.dbo.sysjobschedules    AS js ON js.job_id = j.job_id
JOIN msdb.dbo.sysschedules       AS s  ON s.schedule_id = js.schedule_id
WHERE j.name LIKE N'PerformanceMonitor%';

[pgfiore]

Sorry to bug you again, Erik—and seriously, thanks for being so helpful. I get what you’re saying about the Agent job, but what I’m really after is just straight-up ignoring anything that happens overnight. When you say “the scheduled master collector self-heals on each wake-up,” it kinda sounds like it’ll go back and grab all the stuff it missed while it was off, right? That’s exactly what I don’t want.

I want anything that happens while it’s stopped (like 10pm–6am in your example) to just be gone—like, never collected, never looked at, just completely skipped.

[erikdarlingdata]

@pgfiore Cool, did you want to contribute the code to do that? I am fine accepting code from robot friends if you're not familiar with how to make the changes yourself.

[pgfiore]

I have a bit of experience with SQL and Microsoft SQL Server, but I don’t know this product at all. Its flow looks pretty hard to grasp… but if it’s not urgent, I can try to get a clearer picture of it.
Is there a bit of docs wherever?

[erikdarlingdata]

Honest answer: there isn't a published architecture doc today. The top-level README.md covers install and usage well but doesn't walk through the collection flow. For someone coming from SQL, the most useful path is to read three files in this order — they're the real source of truth and they won't lie to you:

1. install/42_scheduled_master_collector.sql — the heartbeat. SQL Agent job fires this every minute; it reads config.collection_schedule, decides which collectors are due, and dispatches them one at a time. Start here to get the overall loop.

2. Any one collector — pick something straightforward like install/29_collect_default_trace.sql or install/25_collect_query_stats.sql. Every collector follows the same template: validate params → detect first-run → query DMVs → insert to collect.* with dedupe → log to config.collection_log. Once you've read one, you've basically read all of them.

3. config.collection_schedule (table) — the state you're already editing via config.apply_collection_preset. It holds frequency_minutes, enabled, next_run_time, and per-collector flags like collect_plan. Everything the dispatcher decides comes from this table.

That's a 15-minute self-guided tour and covers about 80% of "how does data get from your server to the Dashboard." The remaining 20% is the Dashboard-side rendering and the query path, which is its own chunk and less relevant to what you've been debugging.

No urgency — read when you have time. If you spot things that feel genuinely undocumented rather than just "read the code," open an issue and I'll prioritize based on what comes up.

[erikdarlingdata]

@pgfiore I'm going to close out this specific issue, but please feel free to open a new one when you're ready to work on the Agent job stuff. Thanks again for reporting and following up quickly with me here.