Live-Azure smoke of the pvs_stats Azure SQL Database query variant (#1951)

[erikdarlingdata]

Follow-up deferral from #1951 / PR #1985.

PvsStatsCollector ships two query texts. The on-prem/MI text has been executed against real metal (SQL Server 2025, live ADR database, non-zero PVS, verified row counts). The Azure SQL Database variant has never been run against real Azure. Every claim about it is docs-verified and its shape is pinned by unit tests, but that is not the same as having executed it.

This is not a theoretical worry. While re-verifying the collector during PR #1985, reading the docs turned up a real defect in that exact query text that no test caught and no reviewer had spotted:

• The Azure text joined sys.databases AS d ON d.database_id = pvss.database_id to read the ADR enablement flag.
• On Azure SQL Database those are different id spaces. Microsoft: "In Azure SQL Database, DB_ID may not return the same value as the database_id column in sys.databases" — sys.databases.database_id is unique within the LOGICAL SERVER, while DB_ID() and this DMV's database_id are unique only within a database or elastic pool.
• The INNER JOIN therefore dropped the row and the collector wrote nothing, per database, with no exception and no log line. It would also have matched on some databases and not others, so a fleet would have failed intermittently.

That is fixed in #1985 (the flag is read by name, d.name = DB_NAME(), and the join is gone). But the class of bug it belongs to — an Azure-only path that fails silently and is invisible to every offline test — is exactly what a live smoke exists to catch, and finding one by inspection is evidence there could be another.

What to run

Against a throwaway Azure SQL Database (ADR is always enabled there, so no setup is needed to make the DMV meaningful):

1. Point a collection at the database and let pvs_stats run one cycle.
2. Assert non-zero rows land in the store — the failure mode being guarded is an empty result set, so a green cycle that collected nothing must not read as success.
3. Check is_accelerated_database_recovery_on comes back 1 rather than NULL (that is the column the broken join was reading).
4. Confirm database_data_size_mb is populated from sys.database_files — sys.master_files is not documented for Azure SQL Database, which is why the text forks at all.
5. Confirm the 2022-only column pvs_off_row_page_skipped_oldest_aborted_xdesid binds (the gate treats Azure as always having it).
6. Watch collection_log for the whole cycle — a per-cycle failure here is per-database and easy to miss in aggregate.

Managed Instance takes the on-prem path and is a separate check; the id-space split above is SQL Database only.

Standing recipe and authorization for throwaway Azure runs are in the usual validation notes. This should land before the release that ships #1951.

[erikdarlingdata]

Narrowing the scope, so whoever picks this up does not re-cover ground.

The Azure query text has now been executed — just not on Azure. Every construct it uses (DB_ID(), DB_NAME(), sys.database_files, and the new by-name ADR subquery) is also valid on box, so the text was extracted exactly as BuildQuery resolves it and run against SQL Server 2025 in a real database. It parses, returns exactly one row, binds all 23 columns including the 2022-only one, and the by-name ADR lookup returns 1 rather than NULL. Its sys.database_files size denominator also matched the on-prem text's sys.master_files figure for the same database to the megabyte.

So the following are already covered and do not need a live Azure run: syntax, column bindings, the 2022-only column, one-row-per-call scoping, and the size denominator.

What genuinely still needs Azure is the part that only exists there:

1. That pvss.database_id = DB_ID() selects the intended database on a platform where those ids are pool-scoped.
2. That rows actually land in the store through the real collector path, not just that the SQL returns rows in a query window. An empty result set is the failure mode being guarded, so a cycle that "succeeded" while collecting nothing must not read as success.
3. Elastic pool behavior specifically — the id-space caveat calls out pools by name, and a pooled database is the configuration most likely to expose a remaining assumption.
4. Whether the permissions hold on the lower service objectives (Basic/S0/S1 need ##MS_ServerPerformanceStateReader## membership rather than a plain grant).