我已经写了查询，让我有一个 Azure SQL Server 内的所有 Azure SQL 数据库的概述;此查询需要在master运行：
DECLARE @StartDate date = DATEADD(day, -30, GETDATE()) -- 14 Days
SELECT
database_name AS DatabaseName,
sysso.edition
,sysso.service_objective
,(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
,CAST(MAX(storage_in_megabytes) / 1024 AS DECIMAL(10, 2)) StorageGB
,MIN(end_time) AS StartTime
,MAX(end_time) AS EndTime
,CAST(G(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(G(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(G(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
inner join sys.databases dbs on rs1.database_name = dbs.name
INNER JOIN sys.database_service_objectives sysso on sysso.database_id = dbs.database_id
WHERE start_time > @StartDate
GROUP BY database_name, sysso.edition, sysso.service_objective
ORDER BY database_name , sysso.edition, sysso.service_objective
这里的输出：
我想添加到这个查询每个数据库有多少逻辑 CPU。
我知道有query that I can run on each database，但我想通过查询master数据库来选择每个数据库有多少逻辑 CPU。
有没有办法从sys.表中获取这些信息？
或者动态 SQL 是唯一的出路？
EDIT:@ Charlieface 建议使用SELECT cpu_count FROM sys.dm_os_sys_info这是正确的，但如果我在master上运行它，我只看到 master 上使用的 CPU。我需要检索用于每个数据库的 CPU。
0
添加以下子查询：
(SELECT cpu_count FROM sys.dm_os_sys_info)
每个处理器的更多信息可以从sys.dm_os_nodes获得
看来 Azure 托管实例只返回master的数据。因此，您需要将其插入到临时表或表变量中，然后加入。
DECLARE @sql nvarchar(max);
SELECT @sql = STRING_AGG(CAST('
SELECT ' + d.id + ', cpu_count FROM ' + QUOTENAME(d.name) + '.sys.dm_os_sys_info
' AS nvarchar(max)), '
UNION ALL
' )
FROM sys.databases;
PRINT @sql; --your friend
DECLARE @tmp TABLE (db_id int PRIMARY KEY, cpu_count int NOT NULL);
INSERT @tmp (db_id, cpu_count)
EXEC sp_executesql @sql;
SELECT ....
cpu.cpu_count
FROM ...
LEFT JOIN @tmp cpu ON cpu.db_id = dbs.id;
本站系公益性非盈利分享网址，本文来自用户投稿，不代表码文网立场，如若转载，请注明出处
评论列表（67条）