Which are the most common performance challenges with Microsoft Azure SQL?

Question

I would like to learn more about the common challenges we face with Microsoft Azure SQL.
I have seem several trainings that enforce the investigation idea of increasing immediately the resources and see if the challenge is eliminated, but I am not convinced this should be a good first step.

Answer 1

Intelligent Insights automatically detects performance issues with SQL Database and Managed Instance databases based on query execution wait times, errors, or time-outs. It outputs detected performance patterns to the diagnostics log. Detectable performance patterns are sbelow.

Detectable performance patterns Description for Azure SQL Database and elastic pools Description for databases in Managed Instance
Reaching resource limits Consumption of available resources (DTUs), database worker threads, or database login sessions available on the monitored subscription has reached limits. This is affecting the SQL Database performance.
Consumption of CPU resources is reaching Managed Instance limits. This is affecting the database performance.
Workload increase Workload increase or continuous accumulation of workload on the database was detected. This is affecting the SQL Database performance. Workload increase has been detected. This is affecting the database performance.
Memory pressure Workers that requested memory grants have to wait for memory allocations for statistically significant amounts of time. Or an increased accumulation of workers that requested memory grants exists. This is affecting the SQL Database performance. Workers that have requested memory grants are waiting for memory allocations for a statistically significant amount of time. This is affecting the database performance.
Locking
Excessive database locking was detected affecting the SQL Database performance. Excessive database locking was detected affecting the database performance.
Increased MAXDOP The maximum degree of parallelism option (MAXDOP) has changed affecting the query execution efficiency. This is affecting the SQL Database performance. The maximum degree of parallelism option (MAXDOP) has changed affecting the query execution efficiency. This is affecting the database performance.
Pagelatch contention Multiple threads are concurrently attempting to access the same in-memory data buffer pages resulting in increased wait times and causing pagelatch contention. This is affecting the SQL database the performance. Multiple threads are concurrently attempting to access the same in-memory data buffer pages resulting in increased wait times and causing pagelatch contention. This is affecting database the performance.
Missing Index Missing index was detected affecting the SQL database performance. Missing index was detected affecting the database performance.
New Query New query was detected affecting the overall SQL Database performance. New query was detected affecting the overall database performance.
Increased Wait Statistic Increased database wait times were detected affecting the SQL database performance. Increased database wait times were detected affecting the database performance.
TempDB Contention Multiple threads are trying to access the same TempDB resource causing a bottleneck. This is affecting the SQL Database performance. Multiple threads are trying to access the same TempDB resource causing a bottleneck. This is affecting the database performance.
Elastic pool DTU shortage Shortage of available eDTUs in the elastic pool is affecting SQL Database performance. Not available for Managed Instance as it uses vCore model.
Plan Regression New plan, or a change in the workload of an existing plan was detected. This is affecting the SQL Database performance. New plan, or a change in the workload of an existing plan was detected. This is affecting the database performance.
Database-scoped configuration value change Configuration change on the SQL Database was detected affecting the database performance. Configuration change on the database was detected affecting the database performance.
Slow client Slow application client is unable to consume output from the database fast enough. This is affecting the SQL Database performance. Slow application client is unable to consume output from the database fast enough. This is affecting the database performance.
Pricing tier downgrade Pricing tier downgrade action decreased available resources. This is affecting the SQL Database performance. Pricing tier downgrade action decreased available resources. This is affecting the database performance.

Answer 2

Here is info for heads up : https://learn.microsoft.com/en-us/azure/sql-database/sql-database-intelligent-insights-troubleshoot-performance