Azure SQL Managed Instances (ASMI) is a new deployment option of Azure SQL Database. It is fully compatible with SQL Server on-premises (Enterprise Edition) but significantly reduces the management overhead and TCO of the database.
The Database Performance Analyzer 2019.4 now supports monitoring and performance tuning of ASMI databases.
Below is a walkthrough of screenshots to successfully register an Azure SQL Managed Instance. It shows a new registration wizard for ASMI databases to both establish connectivity as well as Instance and Alert Group assignments.
Azure SQL Managed Instance Registration Wizard
The first step of the registration wizard is to set up a connection by identifying both the instance details as well as the authentication method and credentials. You can also configure special connection properties if required.
After you have identified the connection, you can now assign the Azure SQL Managed Instance to both instance and alert groups.
The registration wizard will give you a summary of your settings to review and change before you finalize the registration.
Successful registration will result in the following:
EXAMPLE analysis usage for an ASMI database:
The following is an example wait trend report from an analyzed Azure SQL Managed Instance database. The report shows that waits dramatically surge around 2 PM and the anomaly report indicates this is also abnormal for 2 PM. There are several query advisories in the Advisors tab.
Clicking on the second query advisory takes us to the following query performance analysis page where we can see the top waits for that query, plans, and metrics as well as both table tuning and query advisors.
Clicking on the "customer" table tuning advisor takes us to the following Table Tuning Advisor report, showing both Azure and DPA table/index recommendations.
If we go back to the query detail page and click the Plan number 6001221602, the execution plan is displayed below as well as advice on what indices would improve its performance.