One task that is often overlooked by DBAs is automated index maintenance in Azure SQL Database. On-premises administrators may be used to the simplicity of maintenance plans, but these are not available for Azure SQL DB (for now).

Luckily, Ola Hallengren has provided SQL Server Maintenance Solution under an MIT license which includes many valuable scripts related to database administration.

Required objects

We only need a couple of objects for index maintenance so instead of downloading the whole solution, you can focus on the following:

  • CommandLog.sql
  • CommandTable.sql
  • IndexOptimize.sql

The IndexOptimize is written for on-premises SQL Server but will also work with Azure SQL DB. A small caveat is that if you want to include it in your Database Project, you will need to remove references to objects that are outside of the scope of Azure SQL DB.

To simplify the SP usage, I’d suggest modifying the default value of the @LogToTable parameter to ‘Y’. You might also want to consider removing the @Databases parameter and adding the following at the beginning of the stored procedure:

DECLARE @Databases nvarchar(max)
SET @Databases = DB_NAME()

This simplifies the usage of the SP by removing the need to explicitly set parameter values. It’s entirely optional and any approach will work, just don’t forget to pass the parameter values if you decide not to modify the code.

Finally, all that’s left is to automate the execution of the stored procedure. We’ll see how to achieve this using ADF, Azure Automation Account, or Elastic Jobs in another post.

Final considerations

Before simply setting the schedule to trigger on daily basis, consider the cost implications and performance benefits.

Index maintenance can be a resource-heavy task and if you’re working in a scalable serverless mode you could incur unexpected costs. On the other hand, fragmented indices are not the end of the world and sometimes you won’t notice any performance benefits.

Additionally, consider querying the CommandLog table to check if any indices are regularly rebuilt; it could indicate an issue with the definition of a clustered index.

There’s no general rule of thumb here on how often index maintenance should be performed, but it’s best not to forget that some rules are different in serverless and dedicated environments.