How can I test what load an Azure SQL Database is under before executing a command?

Question

I want to execute commands when the Azure database is not under high load, i.e. the DTU percentage is lower than say 10%. This query gives me the DTU percentage.

SELECT TOP (1) end_time, (SELECT Max(v)    
FROM (VALUES (avg_cpu_percent), (avg_data_io_percent), (avg_log_write_percent)) AS    
value(v)) AS [avg_DTU_percent]   
FROM sys.dm_db_resource_stats;

The documentation says it updates every 15 seconds. Does that mean that the information is up to 15 seconds out of date? If I run this query immediately before performing an intensive operation will I know that I am not going to max out the DTUs, (accepting that the operation itself does not do that)?

Are there any other ways to check what load the database is under before executing another command?

Alberto Morillo Alberto Morillo · Accepted Answer · 2019-03-15T22:04:47

It helps if you verify each resource consumption individually as shown on below query. You can adjust 80 as the threshold on the query as you wish.

SELECT 
    (COUNT(end_time) - SUM(CASE WHEN avg_cpu_percent > 80 THEN 1 ELSE 0 END) * 1.0) / COUNT(end_time) AS 'CPU Fit Percent'
    ,(COUNT(end_time) - SUM(CASE WHEN avg_log_write_percent > 80 THEN 1 ELSE 0 END) * 1.0) / COUNT(end_time) AS 'Log Write Fit Percent'
    ,(COUNT(end_time) - SUM(CASE WHEN avg_data_io_percent > 80 THEN 1 ELSE 0 END) * 1.0) / COUNT(end_time) AS 'Physical Data Read Fit Percent'
FROM sys.dm_db_resource_stats

For example, if you see avg_log_write_percent is close or equal 100% on your Azure SQL Database then throttling is affecting your database, and it does not matter what the CPU or the other individual measures say. On that situation your SQL Database is under high load and you do not want to add more load.

On below query I added the DTU limit for the current tier of your database, so you can compare the average DTU limit versus the average DTU consumption over the last 5 minutes, and decide if you want to add the extra workload (more instructions to execute) to the current load of your database.

DECLARE @s datetime;  
DECLARE @e datetime;  
SET @s= DateAdd(minute,-5,GetUTCDate());  
SET @e= GETUTCDATE();
SELECT 
 end_time AS [EndTime]
  , (SELECT Max(v) FROM (VALUES (avg_cpu_percent), (avg_data_io_percent), (avg_log_write_percent)) AS value(v)) AS [AvgDTU_Percent]  
  , ((dtu_limit)*((SELECT Max(v) FROM (VALUES (avg_cpu_percent), (avg_data_io_percent), (avg_log_write_percent)) AS value(v))/100.00)) AS [AvgDTUsUsed]
  , dtu_limit AS [DTULimit]
FROM sys.dm_db_resource_stats
WHERE end_time BETWEEN @s AND @e

How can I test what load an Azure SQL Database is under before executing a command?

1 Answers