Azure SQL Analytics solution now in Public Preview. Azure SQL Analytics, collects and visualizes the important SQL Azure performance metrics that enable users to easily create custom monitoring rules and alert on these rules under defined scenarios. The solution, enables you to monitor across multiple Azure subscriptions, resources and elastic pools.
By using the Azure SQL Analytics solution, you can capture metrics from Azure SQL Database and elastic pools and visualize them in Operations Management Suite. This solution takes advantage of Azure Diagnostic metrics and Log Analytics views to present data about all your instances of Azure SQL Database and elastic pools in a single log analytics workspace.
- In the Azure portal, click the Marketplace tile, click Monitoring + Management, and search for Azure SQL Analytics, and then click Azure SQL Analytics in the search results.
- Click the Create button to start the configuration wizard in the Azure portal and configure the solution.
Advanced scenario with multiple subscriptions
Simply provide the workspace resource ID as a parameter when you execute the script to send diagnostic data from resources in one Azure subscription to an OMS workspace in another Azure subscription.
PS C:\> $WSID = "/subscriptions/<subID>/resourcegroups/oms/providers/microsoft.operationalinsights/workspaces/omsws"
PS C:\> .\Enable-AzureRMDiagnostics.ps1 -WSID $WSID
Analyze data and create alerts
The solution ships with a handful of useful queries to get started analyzing data that you find when you go to the solution view and scrolling to the far right.
- Go to http://mms.microsoft.com.
- Authenticate to the OMS workspace that you have configured for this solution.
- Open the solution view for Azure SQL Analytics in your OMS workspace.
- Scroll to the far right, and select the query on which you want to create an alert.
One of the most useful queries that you can perform is to compare the DTU utilization across all Azure SQL Elastic Pools across all your subscriptions. Database Throughput Unit (DTU) provides a way to describe the relative capacity of a performance level of Basic, Standard, and Premium databases and pools. DTUs are based on a blended measure of CPU, memory, reads, and writes.
By running the following query, you can easily tell if you are underutilizing or over utilizing your SQL Azure elastic pools.
Type=AzureMetrics ResourceId=*"/ELASTICPOOLS/"* MetricName=dtu_consumption_percent | measure avg(Average) by Resource | display LineChart
In the following example, we can clearly see one elastic pool has a heavy spike near 100% DTU. We can then use this information to troubleshoot potential recent changes in our environment by using Azure Activity logs.