Azure SQL DB - Improve available_storage_mb value

[dba-leshop]

Feature Request

Opening a feature request kicks off a discussion.

Proposal:

Getting a value closer to what Database data storage panel provides from Azure Portal

Current behavior:

Currently, the computation of available storage (sqlAzureDBProperties) gives a different view of truth and is based on following formula:

select SUM([size]/128 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128) FROM sys.database_files

The output value reported could be improved as follows: 1) The formula includes transaction log space. I think we should get distinct metric values for both Data and Transaction log used space.

2) Assuming we exclude now the TLog space from the equation, the current formula provides one view of truth because it considers only Allocated Space and Space Used in the current Allocated Space. Technically speaking the formula is correct and we can get free space within the allocated space. But we can also get another version of truth (and maybe closer to Azure Portal Database data storage metrics).

The BOL explains the relationship between the different types of storage space for a database and we need to take into account 3 values including database max size (The amount of data space allocated cannot grow beyond the data max size), data space allocated for storing data and database space used by the database.

Let's consider my own context: Azure portal is reported following outcomes: Used space: 707.53GB / Allocated Space: 799.98GB / Maximum storage size: 1TB

So, Azure Portal reports a used space of 69.09% (~30.91% of free space)

By using the formula mentioned above free space will be ~11.5%

SELECT SUM([size]/128 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128) AS FreeSpaceMBCurrentMethod,
SUM(size - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)) * 100. / SUM(size) AS SpaceFreePercentCurrentMethod
FROM sys.databases
WHERE type_desc = 'ROWS';

By introducing database max size, free space can be computed as follows: database max size - database space used and we get a closer result ~30.91%

SELECT (CAST(DATABASEPROPERTYEX(DB_NAME(), 'MaxSizeInBytes') AS BIGINT) / 1024/ 1024 - SUM(CAST(FILEPROPERTY(name, 'SpaceUsed') AS int) / 128)) AS FreeSpaceMBSecondMethod,
(CAST(DATABASEPROPERTYEX(DB_NAME(), 'MaxSizeInBytes') AS BIGINT) / 1024/ 1024 - SUM(CAST(FILEPROPERTY(name, 'SpaceUsed') AS int) / 128))* 100. / (CAST(DATABASEPROPERTYEX(DB_NAME(), 'MaxSizeInBytes') AS BIGINT) / 1024/ 1024) AS SpaceFreePercentSecondMethod
FROM sys.databases
WHERE type_desc = 'ROWS';

Desired behavior:

Having a metric closer to what Azure Portal provides which is technically more realistic from a storage usage point of view

Use case:

To get a more realistic metric value and to avoid creating false positive alerts based on this metric

[reimda]

Hi David, thanks for submitting this issue. You give a good description of the problem and your solution so it looks like the right direction.

Are you able to put together a PR to make the changes?

I don't use SQL Server much so it would help to know what other people think, especially those who have contributed to telegraf's sqlserver input recently. @Trovalo @dupuyjs @denzilribeiro @dimitri-furman Do any of you have a minute to see if this is the right way for telegraf to report available storage on Azure SQL?

[dimitri-furman]

@dba-leshop - yes, for Azure SQL Database it makes more sense to consider data maximum size (DATABASEPROPERTYEX(DB_NAME(), 'MaxSizeInBytes')) than the allocated space. Except for Hyperscale, where this property returns -1 and the "data utilization percentage" metric is undefined. For that reason, it could make more sense to just collect three metrics in absolute values (MiB): data used size, data allocated size, and data maximum size. All three values may be needed in different scenarios. A percentage can be calculated from that if needed depending on the service tier of the database.

[dba-leshop]

@dimitri-furman I agree @reimda I'm able to make a PR if ok for everyone

[reimda]

Thanks @dimitri-furman and @Trovalo.

@dba-leshop looking forward to your PR!