Advice on Azure SQL Database Index Maintenance

Question

Hi,

We have two questions related to Azure SQL Index Maintenance:

(1) We have been using Azure SQL for many years now, and when we first started my understanding was that since SQL Agent didn't exist, and therefore things like maintenance plans which existed for traditional SQL Server didn't exist, it was up to you to schedule any type of SQL maintenance - such as rebuilding indexes, etc. We were curious if this is still more or less accurate? That it is up to you to create something to run on a schedule and rebuild/reorganize any of the fragmented indexes, and other items such as updating statistics?

With things changing so much in the Azure world, we just wanted to make sure we didn't miss anything if there was a better way to do this now a days.

(2) The next question was around the technique of performing the index maintenance. We currently rebuild indexes that exceed a certain fragmentation using the ONLINE option. My understanding is that the ONLINE option tries to minimize any table locking, to avoid any timeout or deadlock errors for other processes that are running during the rebuild operation. But it is not perfect, and you can still run into timeout and deadlock errors (as we have seen). So we try to pause any of our automated tasks which are doing other work (and interacting with the database) during this time to minimize the amount of activity while the index maintenance is happening.

Just curious if our current approach seems like the correct way of doing it, and inline with "best practices", or if there was a different way we should be looking at this, which may eliminate the need of us pausing other work during this time?

Thanks!

Answer 1

Regarding question #1, in this StackOverflow forum thread you will find the official position as described by Conor Cunningham (Microsoft) about indexes and recommended maintenance. On the same forum thread, I provide you my recommendation on how to do the index and statistics maintenance for a better query performance.

Regarding question #2, my suggestion if the database is reaching storage limits and you are close to decide to scale the service tier due to space consumption or if you want to save storage space, schedule maintenance during maintenance window that allows you to perform offline rebuild of indexes, and you will save 16 bytes per row compared to online rebuild.