是否有API可以实时检索DTU消耗情况?我想在我的网站上重建这样的图表。
在Azure门户上看到的大部分内容都可以通过他们的Rest API公开访问,SQL Server DTU是其中之一的指标。
SQL DTU托管在以下URL: https://management.azure.com/subscriptions/{0}/resourceGroups/{1}/providers/Microsoft.Sql/servers/{2}/databases/{3}/providers/microsoft.insights/metrics?metricnames=dtu_consumption_percent&api-version=2018-01-01&Aggregation=Average×pan=PT1M
要访问此终结点,您需要拥有Bearer授权令牌,可通过在Azure Active Directory(AAD)上注册应用程序并使用该应用程序ID和密钥进行身份验证来实现,并获得访问令牌(Bearer令牌)
可用的指标为平均值/最小值/最大值/计数/总和,使用其中之一(将聚合查询字符串值更改为其中之一即可获取相应的值)
您可以根据需要请求数据。例如,要获取过去1分钟的DTU平均值,请在查询字符串变量timespan中使用PT1M。您将获得响应中最近可用的单个记录的DTU平均值(通常比当前时间早1-2分钟)
我不知道有任何API可以做到这一点,但您可以使用以下查询来创建自己的API或在Azure Automation上创建一个运行此查询并将结果保存到数据库以收集性能数据的runbook。您可以根据需要安排每X分钟运行一次。
DECLARE @startTime datetime
DECLARE @endTime datetime
DECLARE @timeGrain int
SET @startTime = GETDATE() - 5
SET @endTime = GETDATE()
SET @timeGrain = 3600
SELECT ((CONVERT(BIGINT, DATEDIFF(day, 0, [end_time])) * 24 * 3600 + DATEDIFF(second, DATEADD(day, DATEDIFF(day, 0, [end_time]), 0), [end_time])) / @timeGrain) * @timeGrain as start_time_interval
, MAX(cpu_percent) as cpu_percent
, MAX(physical_data_read_percent) as physical_data_read_percent
, MAX(log_write_percent) as log_write_percent
, MAX(memory_usage_percent) as memory_usage_percent
, MAX(xtp_storage_percent) as xtp_storage_percent
, MAX(dtu_consumption_percent) as dtu_consumption_percent
, MAX(workers_percent) as workers_percent
, MAX(sessions_percent) as sessions_percent
, MAX(dtu_limit) as dtu_limit
, MAX(dtu_used) as dtu_used
FROM
(SELECT
end_time
, ISNULL(avg_cpu_percent, 0) as cpu_percent
, ISNULL(avg_data_io_percent, 0) as physical_data_read_percent
, ISNULL(avg_log_write_percent, 0) as log_write_percent
, ISNULL(avg_memory_usage_percent, 0) as [memory_usage_percent]
, ISNULL(xtp_storage_percent, 0) as xtp_storage_percent
, ISNULL((SELECT MAX(v) FROM (VALUES (avg_cpu_percent), (avg_data_io_percent), (avg_log_write_percent)) AS value(v)), 0) as dtu_consumption_percent
, ISNULL(max_worker_percent, 0) as workers_percent
, ISNULL(max_session_percent, 0) as sessions_percent
, ISNULL(dtu_limit, 0) as dtu_limit
, ISNULL(dtu_limit, 0) * ISNULL((SELECT MAX(v) FROM (VALUES (avg_cpu_percent), (avg_data_io_percent), (avg_log_write_percent)) AS value(v)), 0) / 100.0 as dtu_used
FROM sys.dm_db_resource_stats
WHERE [end_time] >= @startTime AND [end_time] <= @endTime
) t
GROUP BY ((CONVERT(BIGINT, DATEDIFF(day, 0, [end_time])) * 24 * 3600 + DATEDIFF(second, DATEADD(day, DATEDIFF(day, 0, [end_time]), 0), [end_time])) / @timeGrain) * @timeGrain