Create indexes is taking days

Question

Hi everyone,

I have a question related to the creation of indexes using the action Create index in Azure. There is an SQL database pool with some databases and there are some performance recommendations. They encourage us to create indexes in some tables we have in a production environment with different sizes between 30,000 rows and 4,5 millions rows max. However, it's taking more than a day to complete this operations so we'd like to know how this processs really works, why it's taking so long and how we could improve it to finish it as soon as possible.

Thank you in advance.

Answer 1

As a workaround, select any recommendation on Azure Portal and then click View script. Collect all the recommendations you want on one single T-SQL script, each recommendation on a separate batch (GO statement), and run this T-SQL script against your database(s) to manually apply the recommendations.

Please note that manually applied recommendations will remain active and shown in the list of recommendations for 24-48 hrs. before the system automatically withdraws them. If you would like to remove a recommendation sooner, you can manually discard it.

Answer 2

Apparently the indexes have been created but we didn't know that because the current state of the operation is In progress / Verifying. However, we found this extra doc in which the different states are explained: https://visualbi.com/blogs/microsoft/azure/auto-index-management-azure-sql-database/

Thank you for your help anyway.

Regards.