Monitoring performance Azure SQL Database using dynamic management views

Microsoft Azure SQL Database enables a subset of dynamic management views to diagnose performance problems, which might be caused by blocked or long-running queries, resource bottlenecks, poor query plans, and so on. This topic provides information on how to detect common performance problems by using dynamic management views.

SQL Database partially supports three categories of dynamic management views:

• Database-related dynamic management views.
• Execution-related dynamic management views.
• Transaction-related dynamic management views.

Identify CPU performance issues

If CPU consumption is above 80% for extended periods of time, consider the following troubleshooting steps:

The CPU issue is occurring now

If issue is occurring right now, there are two possible scenarios:

Many individual queries that cumulatively consume high CPU

Use the following query to identify top query hashes:

Long running queries that consume CPU are still running

Use the following query to identify these queries:

The CPU issue occurred in the past

If the issue occurred in the past and you want to do root cause analysis, use Query Store. Users with database access can use T-SQL to query Query Store data. Query Store default configurations use a granularity of 1 hour. Use the following query to look at activity for high CPU consuming queries. This query returns the top 15 CPU consuming queries. Remember to change rsi.start_time >= DATEADD(hour, -2, GETUTCDATE():

Source : https://docs.microsoft.com/en-us/azure/sql-database/sql-database-monitoring-with-dmvs