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 :
| 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 '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
| 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 |
|
| 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 |
|
| Last Execution time |
The time when this query was last executed |
Top CLR Queries and Waits
Top CLR queries
| Parameter | Description |
|---|
| 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
| Parameter | Description |
|---|
| 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 |
|
| 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 |
|
| 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 |
|
| 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 |
