Azure SQL Databse: Criteria to find overprovsioned database

Question

In order to find the overprovisioned database, I would like to know the following

1. When does the Azure SQL Database will be considered as underutilized.
2. What are the parameters will be taken into account in the case of overprovisioned databases.
3. Will it be applicable to both vCore and DTU purchasing models or not.

Please provide the answers for above as it would be helpful to proceed.

Answer 1

I personally love the output of the following query, collect its results periodically and saving them into a table. Later, I love to see the averages and maximum values per hour. This way I can identify patterns of consumption per hour.

SELECT end_time, avg_data_io_percent, avg_log_write_percent
FROM sys.dm_db_resource_stats
ORDER BY end_time DESC;

Using the max and average values I have been able to identify up to 3 different patterns of consumption. For example, I found a customer was requiring P6 computing power during 6:00 am to 7:00 pm on weekdays, and during 7:00 pm hours and 6:00 am a P4 was required. During weekends the consumption pattern was more related to a P2 computing power. On the block of hours that you see low averages probably the database is over provioned and you can save scaling down on that block of hours.

A way to know that you under provisioned a database after scaling it down is to monitor the connection failures and signals of throttling. The following query should give an idea when you need to scale up a database again.

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