As stated earlier, you can create email alerts on metrics you wish to monitor. To create an alert using Azure Portal, in the database overview blade, select Alert Rules under the monitoring section.

In the Alert Rules blade, select Add alert and fill out the alert details to create and set up the alert:

You can also add a webhook to the alert to take preventative action automatically when the alert threshold is reached. For example, let's say you create an alert that sends out an email whenever the database size crosses the 80% (used) threshold.

The administrator sees the email alert and increases the database size so that the customers aren't affected. However, you can automate this by:

The next time the database size percentage is greater than 80%, the alert will send out an email notification to the administrator and will trigger the webhook. This will start the Azure Automation job, and the database size will be increased.

The database size option under the monitoring section provides a chart representation of the current and max database size. It also gives an option to change the database's size:

The change database size link opens the Configure Performance blade, wherein you can change the service tier or change the database size within the same service tier.

Diagnostic settings allow you to collect relevant data such as database wait statistics, timeouts, errors, and blockings so as to troubleshoot performance issues or audit an Azure SQL Database.

The following data can be captured using diagnostics settings:

To enable diagnostic settings, select the database and the server you want to enable it for and then select Turn on diagnostics.

In the Diagnostics settings blade:

The metrics available on the Azure Portal can also be monitored using the following DMVs:

-- Execute in master database
-- Get utilization in last 6 hours for the toystore database
Declare 
    @StartTime DATETIME = DATEADD(HH,-3,GetUTCDate()),
    @EndTime DATETIME = GetUTCDate()
SELECT 
    database_name,
    start_time,
    end_time,
    avg_cpu_percent,
    avg_data_io_percent,
    avg_log_write_percent,
    (
        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.resource_stats   
WHERE database_name = 'toystore' AND 
start_time BETWEEN @StartTime AND @EndTime
ORDER BY avg_cpu_percent desc

SELECT 
    database_name,
    AVG(avg_cpu_percent) AS avg_cpu_percent
FROM sys.resource_stats   
GROUP BY database_name
ORDER BY avg_cpu_percent DESC

-- Get Average CPU, Data IO, Log IO and Memory utilization
-- Execute in toystore database
SELECT    
    AVG(avg_cpu_percent) AS avg_cpu_percent,   
    AVG(avg_data_io_percent) AS avg_data_io_percent,   
    AVG(avg_log_write_percent) AS avg_log_write_percent,   
    AVG(avg_memory_usage_percent) AS avg_memory_usage_percent
FROM sys.dm_db_resource_stats;

-- Get the Average DTU utilization for toystore database
-- Execute in toystore database
SELECT    
   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
ORDER BY end_time DESC

The DMVs used to monitor connections are the same as the ones used to monitor connections in an On-Premises SQL Server, which are sys.dm_exec_connections, sys.dm_exec_sessions, and sys.dm_exec_requests.

The following query returns all sessions for the sqladmin login:

-- Get all sessions for user sqladmin
SELECT 
    session_id, 
    program_name, 
    status,
    reads, 
    writes,
    logical_reads 
from sys.dm_exec_sessions WHERE login_name='sqladmin'

The following query returns all requests for the sqladmin login:

-- Get all the requests for the login sqladmin
SELECT 
    s.session_id,
    s.status AS session_status,
    r.status AS request_status, 
    r.cpu_time, 
    r.total_elapsed_time,
    r.writes,
    r.logical_reads,
    t.Text AS query_batch_text,
    SUBSTRING(t.text, (r.statement_start_offset/2)+1,   
        ((CASE r.statement_end_offset  
          WHEN -1 THEN DATALENGTH(t.text)  
       ELSE r.statement_end_offset 
         END - r.statement_start_offset)/2) + 1) AS running_query_text 
FROM sys.dm_exec_sessions s join  sys.dm_exec_requests r 
ON r.session_id=s.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE s.login_name='sqladmin'

The DMF ( Dynamic Management Function), sys.dm_exec_sql_text, returns the query text for the given sql_handle.

The query_batch_text column returns all the queries being sent as a request in one batch. If you run the workload as mentioned earlier, you will realize that the query_batch_text column contains all the queries specified in the workload.sql file.

The running_query_text column returns the query which is currently being executed. It is calculated using the statement offset start and end values from the sys.dm_exec_requests DMV.

The following DMVs can be used to monitor queries and procedure performance.

SELECT 
    TOP 5 
    (total_worker_time/execution_count)/(1000*1000) AS [Avg CPU Time(Seconds)],  
    SUBSTRING(st.text, (qs.statement_start_offset/2)+1,   
        ((CASE qs.statement_end_offset  
          WHEN -1 THEN DATALENGTH(st.text)  
         ELSE qs.statement_end_offset  
         END - qs.statement_start_offset)/2) + 1) AS statement_text,
    qs.execution_count, 
    (qs.total_elapsed_time/execution_count)/(1000*1000) AS [Avg Duration(Seconds)] 
FROM sys.dm_exec_query_stats AS qs  
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st  
ORDER BY total_worker_time/execution_count DESC;  

SELECT 
    TOP 5 
    (total_worker_time/execution_count)/(1000*1000) AS [Avg CPU Time(Seconds)],  
    SUBSTRING(st.text, (qs.statement_start_offset/2)+1,   
        ((CASE qs.statement_end_offset  
          WHEN -1 THEN DATALENGTH(st.text)  
         ELSE qs.statement_end_offset  
         END - qs.statement_start_offset)/2) + 1) AS statement_text,
    qs.execution_count, 
    (qs.total_elapsed_time/execution_count)/(1000*1000) AS [Avg Duration(Seconds)] 
FROM sys.dm_exec_query_stats AS qs  
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st  
ORDER BY (qs.total_elapsed_time/execution_count) DESC;  

Blocking is a scenario where a query is waiting to acquire a lock on a resource which is already locked by another query. Blocking causes major performance problems and can bring a database to a halt.

The following query returns the blocking details:

-- Get blocked queries
SELECT   
  w.session_id
 ,w.wait_duration_ms
 ,w.wait_type
 ,w.blocking_session_id
 ,w.resource_description
 ,t.text
FROM sys.dm_os_waiting_tasks w
INNER JOIN sys.dm_exec_requests r
ON w.session_id = r.session_id
CROSS APPLY sys.dm_exec_sql_text (r.sql_handle) t
WHERE w.blocking_session_id>0
GO

In order to see the preceding query results, generate a blocking scenario by following the given steps:

Extended Events, introduced in SQL Server 2008, are lightweight methods used to capture diagnostic information in SQL Server.

Extended Events are similar to the SQL Trace, however, they're more lightweight and scalable than SQL trace.

The following are the important components of an Extended Event.

Session

An Extended Event session, when started, captures the specified data for one or more events.

Events

Events are the activities or actions which the data is to be recorded for. For example, sql_statement_starting and sql_statement_completed are the events raised whenever a SQL statement is started or completed on the given database.

Event Fields

Every event has a set of event fields or data points which are recorded whenever the event is triggered. For example, the sql_statement_completed event has a duration event field.

Global Fields

These are the common data points to be recorded whenever the specified event occurs. Examples of global fields are session_id, sql_text, database_name, and database_id.

Target

The target specifies the storage to be used for the capture data. The following targets are allowed in SQL database: