Performance Monitor Darling Tempdb space usage alert

[Javeeditpro]

Can we have the tempdb usage alert for used space instead of reserved space?

[erikdarlingdata]

Claude posting for Erik Darling

Good catch, and a sharper one than the way you asked it — there's a real bug here, not just a missing feature.

The alert is mislabelled. TempDbSpaceInfo.UsedPercent computes TotalReservedMb / CapacityMb, and TotalReservedMb is the sum of the three reserved page counts from tempdb.sys.dm_db_file_space_usage — user objects, internal objects, version store. So the property is called UsedPercent, the alert renders tempdb NN% used, and the number is reserved space. The threshold setting's own doc comment already admits it: "Fire when tempdb reserved space is at/above this % of total." The one surface that's correct is the one nobody reads. That's ours to fix.

But I'm keeping the metric as reserved, and I'd rather explain why than just decline. For an alert whose job is "tempdb is about to run out," reserved is the more honest numerator. Reserved pages are committed to an allocation unit and can't be handed to anything else, so they're consumed capacity whether or not they currently hold rows. A sort or hash spill reserves substantially more than it fills, and it reserves it first — so a reserved-based alert fires while you still have time to do something, and a used-based one fires after the space is already gone. Alarming early is the entire point of the alert.

There's also a practical problem with "used" specifically. sys.dm_db_file_space_usage doesn't expose it — its columns are the reserved page counts, unallocated_extent_page_count and the extent totals. There's no used-pages column to read. The nearest source is sys.dm_db_partition_stats.used_page_count inside tempdb, and that covers user objects only: internal objects (sorts, hashes, spools) and the version store don't appear there at all, and on a busy server those are often the bulk of what's consuming the space. So a "used" figure would be more expensive to collect and incomplete in exactly the cases you'd most want it — a worse exhaustion signal than the reserved one it replaced.

One more thing worth knowing, since it's the other half of the ratio: the denominator CapacityMb is the growth ceiling (SUM(max_size) over tempdb's ROWS files), not the current allocation. That was a genuine bug, fixed in #2515 — dividing by the allocation measures distance to the next autogrow rather than distance to running out, and on Azure SQL Database one ordinary ~57 MB #temp table read 95.7% full against the allocation and 0.09% against the real cap.

So the fix is the label, not the metric. The property name and the alert text will both say what they actually measure.

If you have a case where a used-space number tells you something reserved doesn't, say so on the issue and I'll take it as a separate ask — a distinct "user object space" figure is a more plausible shape than changing this alert's basis.

Tracking it as #3144 so it's on the board where it'll get worked, rather than sitting in Discussions. Closing this as resolved — and thanks for reporting it. The mislabel would have gone unnoticed considerably longer.