我正在創建一個查詢，允許我深入了解 Azure SQL Server 過去 2 周的日志：
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
問題出現了，line max(storage_in_megabytes) AS StorageMB因為它采用了過去 2 周的最大值。這并不代表當前的資料庫大小。
我想使用LAST_VALUE，但我不知道如何使用，因為沒有row_number().
否則一些：
SELECT TOP 1 storage_in_megabytes
,*
FROM sys.resource_stats
ORDER BY end_time DESC
在選擇中，但這也需要由某些東西聚合。
我有點失落...
uj5u.com熱心網友回復：
您可以使用相關的 TOP 1 子查詢來獲取當前存盤大小，如下所示。此示例還按未聚合的資料庫屬性（database_name 和 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;
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/514589.html