The tempdb Space alert's percentage has no size floor, and on Azure SQL Database that makes it fire on one temp table #2515

Found while removing the Azure SQL Database gate on TempDbStatsCollector (#2512). The collection is right and ships; this is the consequence downstream, and it is a policy call rather than a bug fix, so it is filed rather than folded in.

The mechanism

TempDbSpaceInfo.UsedPercent is

TotalReservedMb / (TotalReservedMb + UnallocatedMb) * 100

and AlertEngine.CheckTempDbSpaceAsync fires when that clears TempDbSpaceThresholdPercent (default 80, in both SKUs).

Both halves of that denominator come from tempdb.sys.dm_db_file_space_usage, which reports the tempdb files as currently allocated. On a box with a pre-sized tempdb that denominator is effectively the ceiling, so the ratio is real headroom and the alert means what it says.

On Azure SQL Database it is not. The platform creates tempdb small and autogrows it toward the tier cap: 4 files, ~62 MB total on both 2-vCore tiers measured (GP_S_Gen5_2 and HS_S_Gen5_2, EngineEdition 5). So the ratio measures distance to the next autogrow, not distance to the ceiling.

The measurement

From #2512, on GP_S_Gen5_2, allocating ~57 MB into one #temp table:

One ordinary temp table, and the server is over the 80% default with 60 MB at stake. That is not a tempdb running out of room; that is a tempdb that has not grown yet. Every Azure SQL Database target doing real work would page someone on its first busy minute.

The second threshold, failing the other way

FactScorer.ScoreTempDbVersionStore scores max_version_store_mb at absolute bars — >5000 MB CRITICAL, >2000 HIGH, >1000 MEDIUM — inherited from install/47_create_reporting_views.sql. Those are unreachable while tempdb is at its initial ~62 MB, so the arm scores 0.

This one is self-consistent (a >1 GB version store implies a >1 GB tempdb) and produces no false alerts, so it needs no change. It is worth knowing about anyway, because RCSI is on by default on Azure SQL Database, which makes the version store the tempdb consumer most likely to matter there — and the space-fraction arm is the only thing covering it until tempdb has grown past a gigabyte.

Options

1. Size floor on the alert, mirroring ScorePlanCacheBloat, which already solves exactly this shape: if (fact.Metadata.GetValueOrDefault("single_use_size_mb") < 100.0) return 0.0; — "a high % on a trivially small footprint is noise". Cheapest, platform-neutral, one constant.
2. Ceiling-aware denominator on Azure: compare against the tier's documented max tempdb data size instead of the current file size. Most correct, and the number is not in any DMV the collector reads today — it would need the service objective and a lookup table, which dates.
3. Leave it, document it, let operators tune per fleet. Cheapest of all and the worst, because the default is what almost everyone runs.

What should decide it

Option 1 is the obvious move, and the whole question is the floor value — which is why this is not in #2512. It suppresses alerts existing on-prem and RDS targets get today, and I cannot measure what those customers rely on from here. Note that AlertEngineTests.TempDb_FiresAtThreshold_AndResolutionCarriesTheCurrentPercent uses a fixture totalling exactly 1000 MB, so a 1 GB floor flips an existing pin: the fixture is itself evidence that any floor silently redefines "small" for everyone.

The measurement that should settle it: the distribution of total_reserved_mb + unallocated_mb at the moment UsedPercent >= 80 across collected tempdb_stats history. If real on-prem fires cluster well above the candidate floor, the floor is free.

Whatever floor lands, it belongs on TempDbSpaceInfo next to UsedPercent so Lite and Darling cannot diverge — they share the type and the engine.