Azure DTU database api

Question

Is there an api to retreive in real time the DTU consumption? I want to rebuild a chart like this on my website.

Answer 1

Hope my answer is not too late,

Recently I have been asked to fetch Azure DTU (average metric) using Azure Monitoring API. A sample working code is here (you need to update your Azure subscription/resource group name etc values in appsettings.config file to get it working.

To access the monitoring API's you need to have access token and to generate the token please follow the steps mentioned here

If you are still reading now here is bit of details what is going on behind the scenes.

1. Everything (well most of it) we see on Azure portal is exposed on their Rest API's, SQL Server DTU is one of the metric.

2. SQL DTU is hosted at below 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&timespan=PT1M

3. To hit this endpoint you need to have Bearer authorisation token and that can be achieved by registering an application on Azure Active Directory (AAD) and using that appid and app key to get yourself authenticated and in return you get access token (Bearer token)

4. Available metrics are Average/Minimum/Maximum/Count/Total use either one of them (just change the Aggregation query string value to one of these to get the corresponding value)

5. You can request the data as per your requirement. for example to get DTU average for last 1 minute use PT1M in query string variable timespan. You will get single record in response to most recent DTU Average is available (usually it is 1-2 mins older)

Hope this helps to achieve what you sought.

Answer 2

I don't know about any API doing that but you can use the following query to create your own API or create a runbook on Azure Automation that run this query and save the results on a database for collecting performance data. You can schedule this to every X minutes as you wish.

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