How to implement Ola Hallengren's IndexOptimize script on Azure Automation

  1. Download 3 scripts from here :

    Table : CommandLog.sql

    Procedures: CommandExecute.sql, IndexOptimize.sql

  2. Edit procedure IndexOptimize according to Brent Ozar article. (

     FragmentationHigh = 'INDEX_REBUILD_ONLINE' 
     FragmentationLevel1 = 50
     FragmentationLevel2 = 80

    But I prefer these defaults according to my DB size. (It is so small relative to Brent's DB) And I let the rebuild index offline at night time. Thus, I just changed these two lines.

     FragmentationLevel1 = 15
     FragmentationLevel2 = 70
  3. Define FillFactor paramaters as 90 Define the MaxNumberOfPages and MinNumberOfPages of each request to split jobs.

Why we want to split it in 2 separate jobs ?

Because Azure Automation duration must be max 3 working hours. Azure does not support over 3 hours for automation jobs.

-- small tables

EXEC dbo.IndexOptimize @Databases = 'TestDB', @FillFactor=90, @MaxNumberOfPages=195000, @UpdateStatistics = 'ALL', @OnlyModifiedStatistics = 'Y', @LogToTable = 'Y'

-- large tables. It means about first 10 big tables.

EXEC dbo.IndexOptimize @Databases = 'TestDB', @FillFactor=90, @MinNumberOfPages=195000, @UpdateStatistics = 'ALL', @OnlyModifiedStatistics = 'Y', @LogToTable = 'Y'

To view list of index fragmentation :

SELECT as 'Schema', as 'Table', as 'Index', DDIPS.avg_fragmentation_in_percent, DDIPS.page_count, I.fill_factor FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS DDIPS INNER JOIN sys.tables T on T.object_id = DDIPS.object_id INNER JOIN sys.schemas S on T.schema_id = S.schema_id INNER JOIN sys.indexes I ON I.object_id = DDIPS.object_id AND DDIPS.index_id = I.index_id WHERE DDIPS.database_id = DB_ID() and is not null and DDIPS.page_count > 1000 and DDIPS.avg_fragmentation_in_percent > 0 ORDER BY DDIPS.page_count desc

Azure Automation PowerShell Script :

You can set a schedule for every weekend night (ex: Sunday, 00:30)

$AzureSQLServerName = "YourSERVERname" $AzureSQLDatabaseName = "YourDBname" $AzureSQLServerName = $AzureSQLServerName + "" $Cred = Get-AutomationPSCredential -Name "sqllogin" $SQLOutput = $(Invoke-Sqlcmd -ServerInstance $AzureSQLServerName -Username $Cred.UserName -Password $Cred.GetNetworkCredential().Password -Database $AzureSQLDatabaseName -Query "EXEC dbo.IndexOptimize @Databases = 'YourDBname',@FillFactor=90,@MaxNumberOfPages = 195000,@UpdateStatistics = 'ALL',@OnlyModifiedStatistics = 'Y',@LogToTable = 'Y'" -QueryTimeout 65535 -ConnectionTimeout 60 -Verbose) 4>&1 Write-Output $SQLOutput