Identify missing indexes in Azure SQL Managed Instance using SSMS

One of the hardest problem in data management is finding the indexes that could improve performance of your workload. SQL Server and Azure SQL Database provide information about the missing indexes, but sometime it is hard to find out what indexes should be created.

Azure SQL Database (Singleton) provides Automatic tuning feature that identify missing indexes, create them and validate that they bring some benefit; however, this feature is not available in SQL Server and Azure SQL Database — Managed Instance.

Although Managed Instance don’t have automatic index creation, it is still not hard to find missing index information. Identification of missing indexes is already built into the SQL Server Management Studio (SSMS).

If you connect to your Managed Instance using SQL Server Management Studio you can open built-in Performance Dashboard by right-clicking on the instance in Object browser and going to Reports > Standard Reports > Performance Dashboard. SSMS will show the following report:

Here you can find many useful information such as CPU usage, wait statistics, the most expensive queries bu various criteria, etc. One of the interesting information in the left bottom part of this report is Missing Indexes link. If you click on this link you will see a list of missing indexes that can be created on your instance:

You can copy-paste T-SQL script for the proposed index is it has high score. This approach don’t gives you ability to test what is the improvement and should you retain this index, but it helps you to identify that Managed Instance believes that you might want to create some index to improve performance of your workload.