Showing results for 
Search instead for 
Did you mean: 
Create Post
Product Manager
Product Manager

New in 2019.4! Analyze Azure SQL Managed Instances

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.

1.2 step-Connection information with advanced con props.png

After you have identified the connection, you can now assign the Azure SQL Managed Instance to both instance and alert groups.

2nd step-Group Assignment.png

The registration wizard will give you a summary of your settings to review and change before you finalize the registration.

3th step-Summary.png

Successful registration will result in the following:

4th step-Registered info.png

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.

A Screen Shot 2019-05-16 at 5.14.54 PM.png

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.

B Screen Shot 2019-05-16 at 5.18.23 PM.png

Clicking on the "customer" table tuning advisor takes us to the following Table Tuning Advisor report, showing both Azure and DPA table/index recommendations.

C Screen Shot 2019-05-16 at 5.22.36 PM copy.png

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.

D Screen Shot 2019-05-16 at 5.31.19 PM.png

Tags (1)
0 Kudos