Azure SQL Database sys.resource_stats :: LAST_VALUE(storage_in

I'm creating a query that allows me to dig into the logs of the last 2 weeks of an Azure SQL Server:

DECLARE @StartDate date = DATEADD(day, -30, GETDATE()) -- 7 Days

SELECT DISTINCT
   MAX(database_name) AS DatabaseName
  ,MAX(sku) AS PlatformTier
  ,max(storage_in_megabytes) AS StorageMB 
  ,MIN(end_time) AS StartTime
  ,MAX(end_time) AS EndTime
  ,CAST(AVG(avg_cpu_percent) AS decimal(4,2)) AS Avg_CPU
  ,MAX(avg_cpu_percent) AS Max_CPU
  ,(COUNT(database_name) - SUM(CASE WHEN avg_cpu_percent >= 40 THEN 1 ELSE 0 END) * 1.0) / COUNT(database_name) * 100 AS [CPU Fit %]
  ,CAST(AVG(avg_data_io_percent) AS decimal(4,2)) AS Avg_IO
  ,MAX(avg_data_io_percent) AS Max_IO
  ,(COUNT(database_name) - SUM(CASE WHEN avg_data_io_percent >= 40 THEN 1 ELSE 0 END) * 1.0) / COUNT(database_name) * 100 AS [Data IO Fit %]
  ,CAST(AVG(avg_log_write_percent) AS decimal(4,2)) AS Avg_LogWrite
  ,MAX(avg_log_write_percent) AS Max_LogWrite
  ,(COUNT(database_name) - SUM(CASE WHEN avg_log_write_percent >= 40 THEN 1 ELSE 0 END) * 1.0) / COUNT(database_name) * 100 AS [Log Write Fit %]
  
FROM sys.resource_stats
WHERE start_time > @StartDate
GROUP BY database_name

The problems arrive in the line max(storage_in_megabytes) AS StorageMB because it is taking the Maximum value of the last 2 weeks. That doesn't represent the current database size.

I would like to use LAST_VALUE but I don't know how to as there is no row_number().

Otherwise some:

SELECT TOP 1 storage_in_megabytes
    ,*
FROM sys.resource_stats
ORDER BY end_time DESC

In the select but that too needs to be aggregated by something.

I'm a bit lost...

CodePudding user response：

You can use a correlated TOP 1 subquery to get the current storage size like below. This example also groups by the database attributes that are not aggregated (database_name and sku).

DECLARE @StartDate date = DATEADD(day, -30, GETDATE()) -- 7 Days

SELECT
   database_name AS DatabaseName
  ,sku AS PlatformTier
  ,(SELECT TOP 1 storage_in_megabytes FROM sys.resource_stats AS rs2 WHERE rs2.database_name = rs1.database_name ORDER BY rs2.start_time DESC)  AS StorageMB 
  ,MIN(end_time) AS StartTime
  ,MAX(end_time) AS EndTime
  ,CAST(AVG(avg_cpu_percent) AS decimal(4,2)) AS Avg_CPU
  ,MAX(avg_cpu_percent) AS Max_CPU
  ,(COUNT(database_name) - SUM(CASE WHEN avg_cpu_percent >= 40 THEN 1 ELSE 0 END) * 1.0) / COUNT(database_name) * 100 AS [CPU Fit %]
  ,CAST(AVG(avg_data_io_percent) AS decimal(4,2)) AS Avg_IO
  ,MAX(avg_data_io_percent) AS Max_IO
  ,(COUNT(database_name) - SUM(CASE WHEN avg_data_io_percent >= 40 THEN 1 ELSE 0 END) * 1.0) / COUNT(database_name) * 100 AS [Data IO Fit %]
  ,CAST(AVG(avg_log_write_percent) AS decimal(4,2)) AS Avg_LogWrite
  ,MAX(avg_log_write_percent) AS Max_LogWrite
  ,(COUNT(database_name) - SUM(CASE WHEN avg_log_write_percent >= 40 THEN 1 ELSE 0 END) * 1.0) / COUNT(database_name) * 100 AS [Log Write Fit %]
  
FROM sys.resource_stats AS rs1
WHERE start_time > @StartDate
GROUP BY database_name, sku
ORDER BY database_name, sku;