Add Persistent Version Store Size Metric

[jacobgexigo]

All databases on Azure SQL Database default to ADR enabled and Read Committed Snapshot enabled so their Persistent Version Store can be highly volatile and can grow to consume all free space in a database if there is a high number of changes to the data. It would be nice to see in the Checks section, the size of the PVS either raw or as a % of the max size. We are currently running this as a custom alert on our side but would be great to have it consolidated here as well.

This is the code we use to determine:
;WITH DBSize
AS(
SELECT DB_NAME() DBName,
CAST(SUM(CAST(FILEPROPERTY(name, 'SpaceUsed') AS FLOAT) * 8192.) / 1024 / 1024 AS FLOAT) AS UsedInMB
FROM sys.database_files
WHERE type_desc = 'ROWS'
)
SELECT DB_NAME(database_id) AS database_name, (persistent_version_store_size_kb / 1024.) AS persistent_version_store_size_mb, cte.UsedInMB, (persistent_version_store_size_kb / 1024.)*100/cte.UsedInMB PVSPercentage
FROM sys.dm_tran_persistent_version_store_stats ps
JOIN DBSize cte ON cte.DBName = DB_NAME(database_id)
WHERE persistent_version_store_size_kb > 0 AND database_id = db_id()

Also, Microsoft has confirmed that any database in an elastic pool with a long running query can cause another database in the pool to pause flushing the PVS, letting it grow indefinitely until finished.

[DavidWiseman]

Thanks for the suggestion. I'm considering enabling ADR on one of our environments at some point - If I do I'll be interested in capturing this.