Microsoft Azure SQL Database Monitoring

Microsoft Azure SQL Database- 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. Applications manager provides monitoring of Azure SQL databases performance metrics and query statistics so that the users can be informed of any performance issues happening in their Azure SQL database.

Adding a new Microsoft Azure SQL Database monitor

Supported Versions of Microsoft Azure SQL Database:

Prerequisites for setting up Microsoft Azure SQL Database monitoring: Click here to know the necessary prerequisites to add a Microsoft Azure SQL Database monitor.

  • Click on the New Monitor link in the Applications Manager web client.
  • Select 'Microsoft Azure' under Cloud Apps category.
  • Specify the Display Name of Microsoft Azure monitor.
  • Enter the Subscription ID for your Microsoft Azure subscription.
  • Click the Azure Services textbox and choose 'Azure SQL Databases' from the drop-down menu.
  • Choose the Discovery mode:
    • 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 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.
  • Specify the polling interval in minutes and timeout in seconds

Use the AddMonitor API to add a Microsoft Azure SQL Database Monitor

To add Microsoft Azure monitor using Mode 1 (Azure Organizational Account):

Syntax :

http://[Host]:[Port]/AppManager/xml/AddMonitor?apikey=[APIKEY]&type=MicrosoftAzure&displayname=[DISPLAYNAME]&SubscriptionID=[SUBSCRIPTIONID]&AzureServices=[SERVICES]&DiscoveryMode=[DISCOVERYMODE]&AzureOrgUserName=[USEREMAILOFORGANIZATIONALACCOUNT]&AzureOrgPassword=[PASSWORDOFORGANIZATIONALACCOUNT]

Request parameters : 

FieldDescription
type Type of the monitor you want to add. Value should be 'MicrosoftAzure'
displayname Preferred display name of the monitor
SubscriptionID Subcription ID of the Azure subscription to be monitored 
AzureServices Azure services to be monitored. The possible values are
  • VirtualMachines
  • StorageAccounts
  • SQLDatabases
Provide the values as comma separated
DiscoveryMode Mode of resource discovery. The value should be 'AzureOrgAccount'
AzureOrgUserName Organizational account created in Azure portal
AzureOrgPassword Password of the organizational account

Sample request :

http://apm-k12r2-2:9900/AppManager/xml/AddMonitor?apikey=e820f56394ac554ec4efebc3fa9dd4f0&type=MicrosoftAzure&displayname=AzureRestAPI&SubscriptionID=78da1f5f-c756-481b-aeff-0471c6fbb262&AzureServices=VirtualMachines,StorageAccounts,SQLDatabases&DiscoveryMode=AzureOrgAccount&AzureOrgUserName=apmou@apmoutlook.onmicrosoft.com&AzureOrgPassword=Abc123

To add Microsoft Azure monitor using Mode 2 (AD Application & Service Principal):

Syntax :

http://[HOST]:[PORT]/AppManager/xml/AddMonitor?apikey=[APIKEY]&type=MicrosoftAzure&displayname=[DISPLAYNAME]&SubscriptionID=[SUBSCRIPTIONID]&AzureServices=[SERVICES]&DiscoveryMode=[DISCOVERYMODE]&ClientID=[CLIENTID]&TenantID=[TENANTID]&AppKey=[APPKEY]

Request parameters : 

Field Description
type Type of the monitor you want to add. Value should be 'MicrosoftAzure'
displayname Preferred display name of the monitor
SubscriptionID Subcription ID of the Azure subscription to be monitored 
AzureServices Azure services to be monitored. The possible values are 
  • VirtualMachines
  • StorageAccounts
  • SQLDatabases
Provide the values as comma separated
DiscoveryMode Mode of resource discovery. The value should be 'AzureSPApp'
ClientID Client ID of the AD Application created in Azure portal
TenantID Tenant ID(Directory ID) of Azure Active directory
AppKey Password of the AD Application created in Azure portal

Sample requests :

http://apm-k12r2-2:9900/AppManager/xml/AddMonitor?apikey=e820f56394ac554ec4efebc3fa9dd4f0&type=MicrosoftAzure&displayname=AzureRestAPI&SubscriptionID=790a1f5f-c756-481b-ae8d-0471c6fbb678&AzureServices=VirtualMachines,StorageAccounts,SQLDatabases&DiscoveryMode=AzureSPApp&ClientID=66e1857a-aea5-466a-9007-b0c6cf23e866&TenantID=322392c9-4095-4ad7-9571-b131b85cf2b0&AppKey=856uwpSpCF6/PXTdqYREo8TJt3NrcxOLChBIO2eHHJo=

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.
Microsoft Azure monitor provides a brief detail of the Azure SQL Databases under the given subscription. Click on the monitor name to see all the SQL Database metrics listed under the following tabs:

Monitor Information

ParameterDescription
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

ParameterDescription
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

ParameterDescription
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  
Last Execution time The time when this query was last executed

Top Queries by IO

ParameterDescription
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  
Last Execution time The time when this query was last executed

Top CLR Queries and Waits

Top CLR queries

ParameterDescription
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  
Last Execution time The time when this query was last executed
  

Top Waits by waiting tasks

ParameterDescription
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

ParameterDescription
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  
Last Execution time The time when this query was last executed

Frequently Executed Queries

ParameterDescription
Execution count Number of times this query was executed
Average Execution time(ms) Average execution for this query
Query  
Last Execution time The time when this query was last executed

Most Blocked Queries

ParameterDescription
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  
Last Execution time The time when this query was last executed

Configuration

ParameterDescription
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