Monitor Azure SQL Databases using Azure SQL Analytics (Preview)
Azure SQL Analytics is a cloud monitoring solution for monitoring performance of Azure SQL Databases at scale across multiple elastic pools and subscriptions. It collects and visualizes important Azure SQL Database performance metrics with built-in intelligence for performance troubleshooting on top.
By using metrics that you collect with the solution, you can create custom monitoring rules and alerts. The solution helps you to identify issues at each layer of your application stack. It uses Azure Diagnostic metrics along with Log Analytics views to present data about all your Azure SQL databases and elastic pools in a single Log Analytics workspace. Log Analytics helps you to collect, correlate, and visualize structured and unstructured data.
Currently, this preview solution supports up to 150,000 Azure SQL Databases and 5,000 SQL Elastic Pools per workspace.
For a hands-on overview on using Azure SQL Analytics solution and for typical usage scenarios, see the embedded video:
Azure SQL Analytics is a cloud monitoring solution supporting streaming of diagnostics telemetry for Azure SQL Databases and elastic pools. As it does not use agents to connect to the Log Analytics service, the solution does not support connectivity with Windows, Linux or SCOM resources, see the compatibility table below.
|Azure Diagnostics||Yes||Azure metric and log data are sent to Log Analytics directly by Azure.|
|Azure storage account||No||Log Analytics does not read the data from a storage account.|
|Windows agents||No||Direct Windows agents are not used by the solution.|
|Linux agents||No||Direct Linux agents are not used by the solution.|
|SCOM management group||No||A direct connection from the SCOM agent to Log Analytics is not used by the solution.|
Perform the following steps to add the Azure SQL Analytics solution to your workspace.
- Add the Azure SQL Analytics solution to your workspace from Azure marketplace.
- In the Azure portal, click + Create a resource, then search for Azure SQL Analytics.
- Select Azure SQL Analytics (Preview) from the list
- In the Azure SQL Analytics (Preview) area, click Create.
- In the Create new solution area, create new, or select an existing workspace that you want to add the solution to, and then click Create.
Configure Azure SQL Databases and Elastic Pools to stream diagnostics telemetry
Once you've created Azure SQL Analytics solution in your workspace, in order to monitor performance of Azure SQL Databases and/or Elastic Pools, you will need to configure each of Azure SQL Database and elastic pool resource you wish to monitor to stream its diagnostics telemetry to the solution.
- Enable Azure Diagnostics for your Azure SQL databases and elastic pools and configure them to send their data to Log Analytics.
To configure multiple Azure subscriptions
To support multiple subscriptions, use the PowerShell script from Enable Azure resource metrics logging using PowerShell. Provide the workspace resource ID as a parameter when executing the script to send diagnostic data from resources in one Azure subscription to a workspace in another Azure subscription.
PS C:\> $WSID = "/subscriptions/<subID>/resourcegroups/oms/providers/microsoft.operationalinsights/workspaces/omsws"
PS C:\> .\Enable-AzureRMDiagnostics.ps1 -WSID $WSID
Using the solution
When you add the solution to your workspace, the Azure SQL Analytics tile is added to your workspace, and it appears in Overview. The tile shows the number of Azure SQL databases and Azure SQL elastic pools that the solution is connected to.
Viewing Azure SQL Analytics data
Click on the Azure SQL Analytics tile to open the Azure SQL Analytics dashboard. The dashboard includes the overview of all databases that are monitored through different perspectives. For different perspectives to work, you must enable proper metrics or logs on your SQL resources to be streamed to Azure Log Analytics workspace.
Selecting any of the tiles, opens a drill-down report into the specific perspective. Once the perspective is selected, the drill-down report is opened.
Each perspective provides summaries on subscription, server, elastic pool, and database level. In addition, each perspective shows a perspective specific to the report on the right. Selecting subscription, server, pool, or database from the list continues the drill-down.
|Resource by type||Perspective that counts all the resources monitored. Drill-down provides the summary of DTU and GB metrics.|
|Insights||Provides hierarchical drill-down into Intelligent Insights. Learn more about intelligent insights.|
|Errors||Provides hierarchical drill-down into SQL errors that happened on the databases.|
|Timeouts||Provides hierarchical drill-down into SQL timeouts that happened on the databases.|
|Blockings||Provides hierarchical drill-down into SQL blockings that happened on the databases.|
|Database waits||Provides hierarchical drill-down into SQL wait statistics on the database level. Includes summaries of total waiting time and the waiting time per wait type.|
|Query duration||Provides hierarchical drill-down into the query execution statistics such as query duration, CPU usage, Data IO usage, Log IO usage.|
|Query waits||Provides hierarchical drill-down into the query wait statistics by wait category.|
Intelligent Insights report
Azure SQL Database Intelligent Insights lets you know what is happening with your database performance. All Intelligent Insights collected can be visualized and accessed through the Insights perspective.
Elastic Pool and Database reports
Both Elastic Pools and Databases have their own specific reports which show all the data that is collected for the resource in the specified time.
Through the Query duration and query waits perspectives, you can correlate the performance of any query through the query report. This report compares the query performance across different databases and makes it easy to pinpoint databases that perform the selected query well versus ones that are slow.
Analyze data and create alerts
High DTU on Azure SQL Database
AzureMetrics | where ResourceProvider=="MICROSOFT.SQL" and ResourceId contains "/DATABASES/" and MetricName=="dtu_consumption_percent" | summarize AggregatedValue = max(Maximum) by bin(TimeGenerated, 5m) | render timechart
High DTU on Azure SQL Database Elastic Pool
AzureMetrics | where ResourceProvider=="MICROSOFT.SQL" and ResourceId contains "/ELASTICPOOLS/" and MetricName=="dtu_consumption_percent" | summarize AggregatedValue = max(Maximum) by bin(TimeGenerated, 5m) | render timechart