1. Download 3 scripts from here : https://ola.hallengren.com/downloads.html

   Table : CommandLog.sql

   Procedures: CommandExecute.sql, IndexOptimize.sql

2. Edit procedure IndexOptimize according to Brent Ozar article. (https://www.brentozar.com/archive/2014/12/tweaking-defaults-ola-hallengrens-maintenance-scripts/)

    FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE'
    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 S.name as 'Schema', T.name as 'Table', I.name 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 I.name 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 + ".database.windows.net" $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