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...
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;