ola hallengren @databasesinparallel parameter on Azure SQLServer

Question

I have a serverless azure sql server instance with about 50 databases and I am trying to get the @databasesinparallel parameter to work to reindex all databases at the same time rather than one at a time. I get error The value for the parameter @DatabasesInParallel is not supported. Does this not work for azure serverless instances?

Answer 1

The relevant code section from the IndexOptimize procedure shows there are two situations that can cause that error:

  IF @DatabasesInParallel NOT IN('Y','N') OR @DatabasesInParallel IS NULL
  BEGIN
    INSERT INTO @Errors ([Message], Severity, [State])
    SELECT 'The value for the parameter @DatabasesInParallel is not supported.', 16, 1
  END

  IF @DatabasesInParallel = 'Y' AND SERVERPROPERTY('EngineEdition') = 5
  BEGIN
    INSERT INTO @Errors ([Message], Severity, [State])
    SELECT 'The value for the parameter @DatabasesInParallel is not supported.', 16, 2
  END

Assuming you're passing in 'Y' for the parameter value, the reason for the error is that SERVERPEROPERTY('EngineEdition') is returning 5, which translates to Azure SQL Database.

Azure SQL Database does not support cross-database queries, so when automating Ola Hallengrens procs in Azure SQL DBs, the objects must be created in all target DBs and are executed individually. If you want to run the reindexing in parallel, I would suggest using Azure Elastic Jobs as these run in parallel across all target databases.

There are some limitations, they don't work with Azure SQL Databases using Private Endpoints for example, but for the most part, Elastic Jobs can perform the reindexing jobs just fine.

Some other alternatives:

• Use Azure Automation and PowerShell Workflow to execute IndexOptimize. Workflows can be run in parallel.
• Use a Logic App to execute IndexOptimize in parallel branches across your Azure SQL Databases.