Azure SQL Performance Recommendations taking long time

Question

Yesterday I found 7-8 Index Recommendations for my Azure SQL Databases. I applied few recommendations which is High impact. But it is taking some time - more than 18 hours.

Is it not the recommended way? Or should we apply the recommendations with manually is the recommended way? Please let me know.

Answer 1

Good day @Anuraj

Is it not the recommended way?

You might get different responses for this question, but my short answer is: Definitely and absolutely NO!

As the name implies these are only recommendation which should give you "points to think about" and not automatic solution which you should implement as it is.

You must remember that indexes are not free and each index you add in order to improve specific query, it can reduces the performance of other queries. Moreover, even per specific query in most cases a good DBA (not event expert) might find better solutions.

(1) You should always examine the performance before and after any implementation of index or any other change.

(2) You should always think about the structure of the database and not only how to stick to the current structure and improve it with indexes.

My feeling is that this is a very small database and in your specific case (adding 73 indexes and working under the Azure resources) you might not feel it too much, but your database is now filled with unnecessarily indexes. In more complex cases this will cost you in money (Azure uses), maintenance cost, and performance in general (you might see specific query perform better but in general it might cost more resources which mean you might need to pay more for the service or feel it in performance).

Answer 2

@Anuraj The time to apply the indexes depends on the pricing tier, volume of data in the tables, whether the tables are transactional etc
While 18 hours might seem like a big number, it is being done in a way that doesnt have any impact or downtime to your application.

As for the recommendations, they are generated by looking at the usage patterns on the database.
You even get to measure the impact or improvement by applying these recommendations.
However, if you do not want Azure SQL to automatically apply these for you, you may want to review them and manually apply the ones that you think are more appropriate.

There are several customers that turn on Automatic tuning because Azure SQL can even revert the changes if it observes that the performance gets degraded after applying the recommendation.

--
If an Answer is helpful, please “Accept Answer” or Up-Vote for the same which might be beneficial to other community members reading this thread.