1. Download 3 scripts from here :

   https://ola.hallengren.com/downloads.html

   Create table : CommandLog.sql

   Create 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 :

Before this script, you have to define “sqllogin” to connect the SQL database.

In the “Automation” section in Azure Panel. Find “Credential” (under Shared Resources) and click “Add a credential” named sqllogin

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

This script for PowerShell 7.2 version (the latest version of PS for today) :

# For Powershell 7.2

# Azure SQL Server and Database information
$AzureSQLServerName = "ServerName.database.windows.net"
$AzureSQLDatabaseName = "DBname"

# SQL user information
$Cred = Get-AutomationPSCredential -Name "sqllogin"
$Username = $Cred.UserName
$Password = $Cred.GetNetworkCredential().Password

# SQL query execution function
function Invoke-SqlQuery {
    param (
        [string]$ServerName,
        [string]$DatabaseName,
        [string]$Username,
        [string]$Password,
        [string]$Query
    )

    # SQL connection string
    $ConnectionString = "Data Source=$ServerName;Initial Catalog=$DatabaseName;User ID=$Username;Password=$Password;Connection Timeout=30"

    # Connect to SqlClient
    $connection = New-Object System.Data.SqlClient.SqlConnection
    $connection.ConnectionString = $ConnectionString
    $connection.Open()

    # Execute SQL query
    $command = $connection.CreateCommand()
    $command.CommandText = $Query
    $command.CommandTimeout = 65535  # Timeout (QueryTimeout)

    # Return result
    $adapter = New-Object System.Data.SqlClient.SqlDataAdapter $command
    $dataset = New-Object System.Data.DataSet
    $adapter.Fill($dataset) | Out-Null

    # Close connection
    $connection.Close()

    return $dataset.Tables[0]
}

# Run query
$Query = "exec [dbo].[StoredProcedureName]"
$SQLOutput = Invoke-SqlQuery -ServerName $AzureSQLServerName -DatabaseName $AzureSQLDatabaseName -Username $Username -Password $Password -Query $Query

# Write results
Write-Output $SQLOutput

If you want to use PowerShell 5.1 (older version)

$AzureSQLServerName = "ServerName"
$AzureSQLDatabaseName = "DBname"
$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].[StoredProcedureName]" -QueryTimeout 65535 -ConnectionTimeout 60 -Verbose) 4>&1
Write-Output $SQLOutput