Microsoft Azure SQL Database Monitoring


Microsoft Azure SQL Database Monitoring Tool - An Overview

Azure SQL DB is a relational database-as-a-service (DBaaS) hosted in the Azure cloud. Data is hosted, managed and provisioned in Microsoft data centers. Since organizations constantly deal with large amounts of data at a global scale, it goes without saying that a proper Microsoft Azure SQL database management tool is highly recommended to monitor and understand what is going on while storing and retrieving huge amounts of data constantly on cloud.

Applications Manager's Azure SQL database performance monitoring provides monitoring of Azure SQL performance metrics and query statistics so that users can be informed of any performance issues happening in their Azure SQL database. It eases your monitoring needs by keeping a close watch on Azure SQL database monitoring metrics along with proactive Azure SQL database query monitoring, providing deep insights into the performance of your Azure SQL environments.

In this help document, you will learn how to get started with Azure SQL database performance monitoring by keeping a close watch on various Azure SQL performance metrics with the help of Applications Manager.

Creating a new Microsoft Azure SQL Database monitor

Prerequisites for setting up Microsoft SQL Azure database monitoring: Click here

Using the REST API to add a new Microsoft Azure SQL Database monitor: Click here

Follow the steps given below to create a new monitor:

  1. Click on the New Monitor link in the Applications Manager web client.
  2. Select 'Microsoft Azure' under Cloud Apps category.
  3. Specify the Display Name of Microsoft Azure monitor.
  4. Enter the Subscription ID for your Microsoft Azure subscription.
  5. Click the Azure Services textbox and choose 'Azure SQL Databases' from the drop-down menu.
  6. Choose the Discovery mode:
    • Discovery using AD Application & Service Principal: Refer Prerequisites link and provide the Client ID, Tenant ID & Application key that were obtained in the above mentioned prerequisite step in the New monitor page.
    • Discovery using Azure Organizational Account (Powershell): Refer Prerequisites link and provide the User email & Password fields in New monitor page with the Username & Password of the created Organizational account.
    • Discovery using OAuth: Use the OAuth Provider that was obtained in the prerequisite step in the New monitor page. Refer Prerequisites section.
  7. Specify the polling interval in minutes and timeout in seconds.
  8. Choose the Monitor Group from the combo box with which you want to associate Microsoft Azure SQL server Monitor (optional). You can choose multiple groups to associate your monitor.
  9. Click Add Monitor(s). This discovers Microsoft Azure SQL database server from the network and starts monitoring them.

Monitored Parameters

Go to the Monitors Category View by clicking the Monitors tab. Click on Azure SQL Database under the Cloud Apps table. Displayed is the Azure SQL database bulk configuration view distributed into three tabs:

  • Availability tab gives the Availability history for the past 24 hours or 30 days.
  • Performance tab gives the Health Status and events for the past 24 hours or 30 days.
  • List view enables you to perform bulk admin configurations.

Azure SQL Database Monitoring Metrics

SQL Azure database monitoring tool like Applications Manager eases out your Microsoft Azure SQL database management needs by providing visibility into your Azure SQL cloud environments along with Azure SQL database query monitoring altogether in a single window, providing all the vital Azure SQL database monitoring metrics in the following tabs:

Monitor Information

Parameter Description
Database Name Name of the Azure SQL Database
Database Username Username of the SQL Server of this particular database 
Database Server Name of the SQL server
Diagnostics Storage Account Storage account to which the SQL database's diagnostics logging is made
Resource Group Name Resource group to which the SQL database is associated
Timeout Timeout Duration(seconds)
Associated Groups The monitor groups to which this monitor is associated
Last Polled At Specifies the time at which the last poll was performed
Next Poll At Specifies the time at which the next poll is scheduled

Overview

Parameter Description
RESOURCE UTILIZATION
CPU Utilization Percentage of CPU utilization
DTU Utilization Percentage of the total DTU used
IO Utilization Data IO percentage
DTU UTILIZATION
DTU Limit Total DTU assigned to this database
DTU Used DTU used by the database
DATABASE SIZE
Used Database Current database used(MB)
Total Database Size Total database size
Storage Utilization Database size percentage
CONNECTIONS
Successful connections Count of successful connections to the database
Failed connections Count of failed connections to the database
Connections Blocked by firewall Count of the connections that are blocked by firewall
PERFORMANCE STATISTICS
Deadlocks Count of deadlocks
Log IO Percentage Percentage of write resource utilization
In-memory OLTP storage used percentage Percentage of OLTP storage used
Workers(concurrent requests) used percentage Percentage of maximum concurrent workers(logins)
Sessions used percentage Percentage of concurrent sessions

Top Queries by CPU

Parameter Description
Average CPU time(ms) Average CPU time taken to execute this query
Total CPU time(ms) Total CPU time taken to execute this query
Average Execution time(ms) Average time taken to execute this query
Query Indicates the query executed.
Last Execution time The time when this query was last executed

Top Queries by IO

Parameter Description
Average IO Average IO operations for this query
Total IO Total IO operations for this query
Average Execution time(ms) Average time taken to execute this query
Query Indicates the query executed.
Last Execution time The time when this query was last executed

Top CLR Queries and Waits

Parameter Description
Top CLR queries
Average CLR time(ms) Average CLR time taken to execute this query
Total CLR time(ms) Total CLR time taken to execute this query
Average Execution time(ms) Average time taken to execute this query
Query Indicates the query executed.
Last Execution time The time when this query was last executed
Top Waits by waiting tasks
Waiting tasks Number of waiting tasks
Wait time(ms) Total wait time
Average Wait time(ms) Average wait time by the waiting tasks
Signal time(ms) Signal time by the waiting task
Wait type Type of the waiting tasks

Top Slow Running Queries

Parameter Description
Average Execution Time(ms) Average execution time for this query
Maximum Execution Time(ms) Maximum execution time for this query
Minimum Execution time(ms) Minimum execution time for this query
Number of Executions Number of times this query was executed
Query Indicates the query executed.
Last Execution time The time when this query was last executed

Frequently Executed Queries

Parameter Description
Execution count Number of times this query was executed
Average Execution time(ms) Average execution for this query
Query Indicates the query executed.
Last Execution time The time when this query was last executed

Most Blocked Queries

Parameter Description
Average time blocked(ms) Average time this query was blocked
Total time blocked(ms) Total time this query was blocked
Average Execution time(ms) Average time taken to execute this query
Query Indicates the query executed.
Last Execution time The time when this query was last executed

Configuration

Parameter Description
Database name Name of the Azure SQL Database
Database status Status of the SQL database
Location Location of the SQL database
Database Server Name of the SQL Server
Resource Group name Resource group to which the SQL database is associated
Database Edition Edition/Pricing tier of this database
Creation date Date on when this database was created
Earliest restore date Oldest restore point
Elastic Pool The Elastic pool to this this database is associated if any